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

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

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

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

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

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

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

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

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