Ask me anything about Microsoft Excel - Page 25 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Two Plus Two Forums 2+2 Communities Other Other Topics

Ask me anything about Microsoft Excel

Post Reply Subscribe

...

Page 25 of 120

First

5 15 20 21 22 23 24 25 26 27 28 29 30 35 45 75

Last

Page 25 of 120

First

5 15 20 21 22 23 24 25 26 27 28 29 30 35 45 75

Last

01-31-2012 , 08:48 AM

#601

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by CopTHIS

Appologies if it's been raised already but is there an easy way to stop cells defaulting to wrap format when you paste into them?

Quoting so it doesnt get lost on the next page, but dont have an answer.

I got a question myself regarding formular1c1:

Quote:

jCell.FormulaR1C1 = "=sumif(B:B," & Chr(34) & "Umsatz IST" & Chr(34) & ",C[]:C[])"

My output is:
=sumif(B:(B),"Umsatz IST",C:C)

Why is he wrapping the second B in parentheses?

Quote

01-31-2012 , 10:24 AM

#602

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Spurious

My output is:
=sumif(B

B),"Umsatz IST",C:C)

Why is he wrapping the second B in parentheses?

solved it, wasnt aware that I could use R2C2 and make the range fix.

Quote

01-31-2012 , 01:38 PM

#603

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by CopTHIS

Appologies if it's been raised already but is there an easy way to stop cells defaulting to wrap format when you paste into them?

Umm I'm not sure, if you manually set the row height (right click on a row > row height, press enter) then when you paste it shouldn't resize because excel assumes it is a fixed row height. I don't really love this solution though, if it something you do a lot in the same workbook you could write a small macro that automatically undoes it

something like

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 target.wraptext = false
 rows(target.row).autofit
End Sub

Quote

01-31-2012 , 01:42 PM

#604

CopTHIS

Pooh-Bah

Join Date: Jan 2006 Posts: 4,094

Thanks, I'll give it a try. It's just one of those silly little things that annoys me with Excel.

Quote

02-05-2012 , 05:04 PM

#605

SMA775

enthusiast

Join Date: May 2011 Posts: 96

Hi zomg,

Just wanted to say thanks for starting this thread. Very informative. I re-read
the thread start-to-finish about once a month.

What's the best way to find the table numbers when you want to download only specific tables from a website when doing a query?

Thanks and cheers!

Quote

02-05-2012 , 05:16 PM

#606

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Thanks!

I usually do record macro then pick the table then see the code generated. I guess you could probably count but that never seems to work for me

p.s. you saved the thread from the archive

Quote

02-06-2012 , 01:29 PM

#607

SMA775

enthusiast

Join Date: May 2011 Posts: 96

Thanks man. Exactly what I was looking for. I was using the countlng method, didn't work too well.

Glad the thread won't be archived.

Have a wonderful day!

Quote

02-07-2012 , 01:07 PM

#608

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Is it possible to copy a data table in an Access database using Excel VBA?

I basically want to make backups from a table by clicking a button in Excel.

Quote

02-07-2012 , 01:15 PM

#609

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Spurious

Is it possible to copy a data table in an Access database using Excel VBA?

I basically want to make backups from a table by clicking a button in Excel.

Definitely!

Code:

Sub AccessToExcel() 
    Dim db As Database
    Dim rs As Recordset
    Dim currentRow as long
    Dim ws as worksheet
    Set db = OpenDatabase("C:\yourdatabaselocation") 
    Set rs = db.OpenRecordset("SELECT * from table;")
    Set ws = thisworkbook.sheets("Sheet1") 
    currentRow = 2
    Do While NOT rs.EOF
            ws.range("A" & currentRow) = rs!id
            ws.range("B" & currentRow) = rs!name
            currentRow = currentRow + 1
            rs.movenext
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

You will need to make sure you have the appropriate references set in Excel by going to Tools > References in the VBA editor (it's called DAO library or something)

EDIT: FYI. if it's a really large table and you are going to be doing it often there are better/faster ways to do it such as storing all the data in an array then dumping it into excel, turning off screenupdating/calculation

Quote

02-07-2012 , 01:21 PM

#610

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

No, I dont want it in an Excel file. I expressed myself incorrectly.

Basically, I got a data table in an Access database called Data and now I want to copy that table within Access as a backup and call it Data_BackUp.

Is that possible?

Quote

02-07-2012 , 01:27 PM

#611

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Ohhhh right.

I think the only way is to make a copy of the table which can be tricky if you have indexes and a lot of customization.

It looks like everything you need is here:

http://support.microsoft.com/kb/217011

and you should only need to make some small modification. Obv let me know if you need more

Quote

02-07-2012 , 02:25 PM

#612

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

I am using ADODB, is DAO the same thing?

Quote

02-07-2012 , 02:32 PM

#613

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

You can use both in the same application, i'm not sure all the commands you need for this are available in ADO but it shouldn't be a problem to write this using DAO. In general you are correct to be using ADODB in Excel

Quote

02-07-2012 , 04:38 PM

#614

alpinestyle

centurion

Join Date: Jun 2005 Posts: 180

Hello All,

I am trying to "solve a right triangle". I can quite easily solve all 3 side lengths.

Having trouble inputing formulas to solve for the 2 unknown angles.

THanks

Quote

02-07-2012 , 04:53 PM

#615

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Hmm my trigonometry is admittedly hazy (SOHCAHTOA ftl) but excel has sin/cos/tan functions but it works in radians so you need to convert to degrees using DEGREES()

Quote

02-08-2012 , 11:43 AM

#616

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

Hi, hoping someone in here can help me.

I'm using openoffice fwiw but I guess they are quite similar..

What I want to do is, take a list looking like this:

aaaa 50
bbbb 45
cccc 30
dddd 10

then paste it into a sheet and get the following output:

aaaa: XXX (can do that manually ofc)
bbbb: -5
cccc: -15
dddd: -20

so basically sorted by the number and then displaying the difference to the next highest number.

if that isn't easily possible I would like it to give:

aaaa: XXX
bbbb: -5
cccc: -20
dddd: -40

so just taking the number and giving the difference to the highest number in the list.

any help much appreciated!

Quote

02-08-2012 , 11:55 AM

#617

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

subandi, not sure what you are trying to do there.
Does the new sheet have any of the parameters given?
And why are you trying to do this with a formula and not a pivot table?

zomg,
I got a question myself. As previously stated here, I am working on a Excel/Access Tool.

I got a macro that reads out roughly 2500 datasets.
The thing is, I open a new connection and recordset every single time I get one new dataset, so this might or might not slow down the process significantly.

Would it drastically improve, if I'd open one connection at the beginning and use that throughout?

Quote

02-08-2012 , 11:59 AM

#618

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

Hm,

I don't know what a pivot table is, guess that is part of the reason I'm not trying to do it with one

it's for a rake race, I will have the player's names and their rake figures but I want to make it so that it's not instantly obvious who raked how much, while still allowing every player to see instantly where he stands in respect to the player above him and also deduct with a little work if he wants how much he is behind to the top guy.

Quote

02-08-2012 , 01:05 PM

#619

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

Quote:

Originally Posted by alpinestyle

Hello All,

I am trying to "solve a right triangle". I can quite easily solve all 3 side lengths.

Having trouble inputing formulas to solve for the 2 unknown angles.

THanks

If you know all 3 side lengths you can use either

=DEGREES(ASIN([opposite length]/[hyp]))

or

=DEGREES(ACOS([adjacent length]/[hyp]))

Quote

02-08-2012 , 02:59 PM

#620

Yeti

Abominable

Join Date: Jun 2004 Posts: 23,208

i need to write a macro that adds more data to an existing graph (from a table already existing in the workbook), chooses colours, writes the labels etc. that's the short version. assume this is possible in VBa? i haven't tried yet, will have a play around later with the record function. i got a book and have still not really delved into it yet

Quote

02-08-2012 , 03:09 PM

#621

noname6520

veteran

Join Date: Feb 2008 Posts: 3,414

Public Function CheckName(name As String) As Integer
Dim i As Integer
For i = 1 To 5
If Worksheets("Sheet1").Range("B" & i) = name Then
MsgBox (name & " is located on row " & i)
CheckName = i
End If
Next i
End Function

anyone know why I am still getting a #Name result?

thanks in advance

zomg this is the solution you helped me with in the other subforum I am still getting the same #Name result I was getting before tho

Quote

02-08-2012 , 03:29 PM

#622

Harruin

grinder

Join Date: Jul 2005 Posts: 564

Are you sure that name is being passed along properly?

Quote

02-08-2012 , 03:40 PM

#623

noname6520

veteran

Join Date: Feb 2008 Posts: 3,414

Quote:

Originally Posted by Harruin

Are you sure that name is being passed along properly?

uh i just called it like this:

=checkname("A")

Quote

02-08-2012 , 04:34 PM

#624

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Been quite busy today, will have a look at everything shortly

Quote

02-08-2012 , 04:58 PM

#625

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by subandi

Ok not sure if this will work in OO, if it doesn't let me know and i'll download it and try there

Assuming your first range is in cells A1:B4 and your second range is in cells D1:E4 so the formula would start in E2 then be copied down to E4 and E1 would be xxx

Code:

=VLOOKUP(D2,$A$1:$B$4,2,0)-MAX(B1:$B$4)

Quote

Page 25 of 120

First

5 15 20 21 22 23 24 25 26 27 28 29 30 35 45 75

Last

Post Reply Subscribe

...

Page 25 of 120

First

5 15 20 21 22 23 24 25 26 27 28 29 30 35 45 75

Last