Variable

Resolved · Urgent Priority · Version 2003

Sun 26 Jul 2009, 12:33 Delegate Danielle said...

Danielle has attended:
Excel VBA Intro Intermediate course

Variable

Hi there

I am trying to create a macro and it keeps de-bugging at this point. I am trying to select the whole table of information. If I hover of the IntNumRows it says it = 0, do I need to change the value of the IntTargetRow from 2 to 1?

IntTargetRow = 2
IntNumRows = Sheets("JPY TRADES").Range("A2").CurrentRegion.Rows.Count

Please advise as soon as possible.

Thanks
Danielle

Mon 27 Jul 2009, 10:46 Trainer Anthony said...

RE: Variable

Hi Danielle, thanks for the query. Firsly, changing the value of the IntTargetRow from 2 to 1 in the code above won't make any difference because InTargetRow is not referenced in the code for IntNumRows. Secondly, make sure you cite the workbook name in the IntNumRows code and perhaps try bracketing it within a With Statement.

With Workbooks("workbook1.xls").Sheets("JPY TRADES")
.Range("a2").CurrentRegion.Rows.Count
End With

Also try the following:

Worksheets("Sheet1").Activate
ActiveCell.CurrentRegion.Select

...(note that it's the *Worksheets* object) and:

Range("A1").CurrentRegion.Select

...just to test that CurrentRegion is operating correctly, and if it is cannibalise the code if you need to. Lastly, the code may be falling over because of the space between "JPY" and "TRADES". A long shot, but try using an underscore, update the code and see what happens.

Let me know how you get on.

Anthony