How to refer (Syntax) a variable name (cell address) in a range(varname)

[Gonfer]

Hi guys, I will appreciate any suggestion.

I am reading the fields of a record set from the first field to the last one in order to write the data of each field into different cell addresses in a spreadsheet. What I need to know what is the correct syntax to refer correctly my variable name "CellTarget" containing the cell address (column letter and the row number) in the instruction :

Worksheets("Form").Range(CellTarget).Value = Rs.Fields(i)

I need this instruction to assign the value from any Rs.Fields(i) to a cell address, but referring to the name of my variable.

Works perfect in this way :
Worksheets("Form").Range("$E$6").Value = Rs.Fields(i)

I need to get the same result using my variable name?
Ex. Celltarget ="$E$6"
Worksheets("Form").Range(CellTarget).Value = Rs.Fields(i)

Obviously if I have tried to work the code like it is stated in the last line above and it gives me an error. Looks like it is matter of some syntax issue.


Thanks so much in advance for any advice

GonFer

 

[combo]

Embed reference, Value is default Range property:
[pre]With Worksheets("Form")
.Range(.Range(CellTarget)) = Rs.Fields(i)
End With[/pre]

combo

 

[SkipVought]

GonFer, you might want to consider assigning Range Names to the cells in your form, names that are meaningful, like First_Name. Just north of column A is a box where you would see the active cell reference. That's the Name Box. Select cell E6, select in the Name Box and enter the relevant name for that cell.

Let's say that E6 is First_Name. Then your code could be...

[First_Name].Value = Rs.Fields(i)

Then if I were doing it, I'd build a table on a separate reference sheet, that contained all the Range Names for my form in the same order that they are in the database query you're executing and if my reference list range name for that list was FormNames, then...

For Each fld in Rs.Fields
   i = i + 1
   Range(Range("FormNames")(i).Value).Value = fld.Value
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]