variable trouble

[Kieran1028]

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

 

[Chip Pearson]

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]

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

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

 

[Chip Pearson]

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

 

[anilsolipuram]

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

 

[Kieran1028]

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

 

[Chip Pearson]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[anilsolipuram]

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

 

[Kieran1028]

Thanks, coercing all terms to a string variable seems to have worked. I
didn't know you could do that.

Thanks again,
Kieran