Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
I guess I don't know the proper way to reference variables in code.. can anyone troubleshoot this routine for me? GREATLY appreciated... get errors almost every time I try to use a variable in code. fo example, Workbooks(fname).Activate gives me an error. If I eliminate this cod by just activating a specific workbook instead of the one defined b fname, I get an error somewhere else where I tried to use a variable like at Set destCell = destWks.Cells(3, dcol) where VB says I didn't define th variable yet. Sub get1degdata() ' ' get1degdata Macro ' Macro recorded 11/19/2004 by Kieran Coghlan ' ' Dim ramp As Variant Dim toes As Variant Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet Dim destWks As Worksheet Dim destCell As Range Dim dcol As Integer fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Workbooks.Open (fname) Workbooks(fname).Activate ' Sheets("charts").Select Set Wkbk = Workbooks(fname) ramp = InputBox("Enter the ramp duration in ms: 500, 1000, 2000, o 4000") toes = InputBox("Enter the toes direction, UP or DOWN") Sheets.Add.Name = ramp Worksheets(ramp).Select dcol = 1 For Each wksht In Wkbk.Worksheets If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then Set destWks = Wkbk.Worksheets(ramp) wksht.Range("q12:q2011").Copy End If Set destCell = destWks.Cells(3, dcol) destCell.PasteSpecial Paste:=xlPasteValues, Operation _ :=xlNone, SkipBlanks:=False, Transpose:=False dcol = dcol + 1 Next End Sub Thanks, Kiera -- Kieran102 ----------------------------------------------------------------------- Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
In your code, fname contains the entire full file name. You
can't use this in the Workbooks collection; you need only the file name without the directory path. I would write your code as Dim FName As String Dim WB As Workbook FName = Application.GetOpenFilename("Excel files (*.xls),*.xls") Set WB = Workbooks.Open(FName) WB.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... I guess I don't know the proper way to reference variables in code... can anyone troubleshoot this routine for me? GREATLY appreciated... I get errors almost every time I try to use a variable in code. for example, Workbooks(fname).Activate gives me an error. If I eliminate this code by just activating a specific workbook instead of the one defined by fname, I get an error somewhere else where I tried to use a variable, like at Set destCell = destWks.Cells(3, dcol) where VB says I didn't define the variable yet. Sub get1degdata() ' ' get1degdata Macro ' Macro recorded 11/19/2004 by Kieran Coghlan ' ' Dim ramp As Variant Dim toes As Variant Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet Dim destWks As Worksheet Dim destCell As Range Dim dcol As Integer fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Workbooks.Open (fname) Workbooks(fname).Activate ' Sheets("charts").Select Set Wkbk = Workbooks(fname) ramp = InputBox("Enter the ramp duration in ms: 500, 1000, 2000, or 4000") toes = InputBox("Enter the toes direction, UP or DOWN") Sheets.Add.Name = ramp Worksheets(ramp).Select dcol = 1 For Each wksht In Wkbk.Worksheets If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then Set destWks = Wkbk.Worksheets(ramp) wksht.Range("q12:q2011").Copy End If Set destCell = destWks.Cells(3, dcol) destCell.PasteSpecial Paste:=xlPasteValues, Operation _ :=xlNone, SkipBlanks:=False, Transpose:=False dcol = dcol + 1 Next End Sub Thanks, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=319361 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
Chip Pearson Wrote: In your code, fname contains the entire full file name. You can't us this in the Workbooks collection; you need only the file name withou the directory path. I would write your code as Dim FName As String Dim WB As Workbook FName = Application.GetOpenFilename("Excel files (*.xls),*.xls") Set WB = Workbooks.Open(FName) WB.Activate -- Cordially, Chip Pearson Chip, thanks, that worked for the first problem. However, I still ge an error at the following line: Set destCell = destWks.Cells(3, dcol) ...and I can't figure out why. Anyone have any ideas? The specifi error is Run-time error 91: "Object Variable or With block variable no set". Thanks again, Kiera -- Kieran102 ----------------------------------------------------------------------- Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
That error indicates that you have not Set the variable to the
appropriate worksheet. Step through your code (F8) and you'll find that you are not executing the line of code that sets the destWks variable. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... Chip Pearson Wrote: In your code, fname contains the entire full file name. You can't use this in the Workbooks collection; you need only the file name without the directory path. I would write your code as Dim FName As String Dim WB As Workbook FName = Application.GetOpenFilename("Excel files (*.xls),*.xls") Set WB = Workbooks.Open(FName) WB.Activate -- Cordially, Chip Pearson Chip, thanks, that worked for the first problem. However, I still get an error at the following line: Set destCell = destWks.Cells(3, dcol) ..and I can't figure out why. Anyone have any ideas? The specific error is Run-time error 91: "Object Variable or With block variable not set". Thanks again, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=319361 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then should be If wksht.Range("K5").value = ramp And wksht.Range("G7").value = toe The -- anilsolipura ----------------------------------------------------------------------- anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
Hmmm... that seemed to help, as the program ran a lot longer, seemingl searching all the sheets, but in the end I got the same error... Is there something wrong with my for/next loop structure or somethin else maybe -- Kieran102 ----------------------------------------------------------------------- Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
Since Value is the default property of a Range object, it can be
safely omitted. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "anilsolipuram" wrote in message ... If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then should be If wksht.Range("K5").value = ramp And wksht.Range("G7").value = toes Then -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=319361 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
Sub get1degdata() ' ' get1degdata Macro ' Macro recorded 11/19/2004 by Kieran Coghlan ' ' Dim ramp As Variant Dim toes As Variant Dim fname As Variant Dim Wb As Workbook Dim wksht As Worksheet Dim destWks As Worksheet Dim destCell As Range Dim dcol As Integer fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wb = Workbooks.Open(fname) Wb.Activate MsgBox fname ' Sheets("charts").Select ramp = InputBox("Enter the ramp duration in ms: 500, 1000, 2000, o 4000") toes = InputBox("Enter the toes direction, UP or DOWN") Sheets.Add.Name = ramp Worksheets(ramp).Select dcol = 1 Dim pw As Integer pw = 0 For Each wksht In Wb.Worksheets If CStr(wksht.Range("K5").Value) = CStr(ramp) An CStr(wksht.Range("G7").Value) = CStr(toes) Then Set destWks = Wb.Worksheets(ramp) wksht.Range("q12:q2011").Copy pw = 1 End If If (pw = 1) Then Set destCell = destWks.Cells(3, dcol) destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone SkipBlanks:=False, Transpose:=False MsgBox "pasted" dcol = dcol + 1 pw = 0 End If Next End Su -- anilsolipura ----------------------------------------------------------------------- anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627 View this thread: http://www.excelforum.com/showthread.php?threadid=31936 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable trouble
Thanks, coercing all terms to a string variable seems to have worked. I didn't know you could do that. Thanks again, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=319361 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having trouble getting MATCH to work with a variable lookup array | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Trouble with variable type | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Trouble setting variable as filename with date | Excel Programming |