Using a variable as a cell address

[rickgalty]

Hi, I am looking at a list of options using a "Select Case" function in VBA, and based on the result, want to put data in a certain row on the spreadsheet. I could put

Case 1
Range("h3").Value = txtDate.Text
Range("i3").Value = txtTime.Text
Case 2
Range("h4").Value = txtDate.Text
Range("i4").Value = txtTime.Text

etc etc, but theres several data items for each line, and bunch of possible lines, so that would get unwieldy. I'd rather do something like..

Case 1
RowNo = 3
Case 2
RowNo =4
Range("h'RowNo'").Value = txtDate.Text

But I don't know how. Any tips on using a variable as part of a cell address ?

Thanks,
Richard

 

[PHV]

Range("h" & RowNo).Value = txtDate.Text

You may also consider using Cells.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Take a look at the Cells method...

dim r as long, c as integer
r = 4   'row 4
c = 8   'column H
Activesheet.cells(r, c).value = tstDate.Text
Activesheet.cells(r, "H").value = tstDate.Text

give identical results.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[firefytr]

<quoting...>
Activesheet.cells(r, "H").value = tstDate.Text
</quoting>

I highly recommend against this practice of mixing text within the Cells method, it doesn't mesh well with Excel 12. I would change the column letters for their respective numbers; or use the Range method instead.

-----------
Regards,
Zack Barresse

 

[SkipVought]

Hey Zack,

So how does it mesh with other versions of Excel?

It certainly is not my first recommendation, but it is an option in the versions that I have been using.

Where did you get your recommendation?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[firefytr]

I got mine when it failed on my copy of Office 12. I don't know if they will fix it or not, but it's a compile error if they don't. Real PITA. As far as other versions, it shouldn't be a problem (at least back to 97) that I'm aware of.

-----------
Regards,
Zack Barresse