INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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


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

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

combo

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

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...

CODE

[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...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close