Code that inputs a value into a wrksht Range (cell) as directed from "address" cell

[JasonRay]

Hi,

I understand how to input data into a range via code [....Range ("A1"). Value = 30], but what I really am looking for is a way to say [..............Range ("input data into the cell that Range ("A1") gives you the address of"). Value = 30].

I would appreciate anyone that knows how to do this to give me a little advice.

Thank you,
JasonRay

[davesexcel]

If you had B1 in cell A1 this code will put 30 in B1

Please Login or Register  to view this content.

[AlphaFrog]

Try something like...

Please Login or Register  to view this content.

[JasonRay]

Seems pretty straight forward, but something is glitching it up. The following is the code I tried; (as an aside, I have asked a number of people in here to let me know how to insert code in this forum in the pretty boxes you guys use and also how to color the font, but no one seems to want to answer..........could you tell me?)

Sub M016_CheckBox1_Click()
Dim ws As Worksheet
Set wsa = Worksheets("Bank Stmt")
Set wsb = Worksheets("Bank Snapshot")

If wsa.Range("BI9") = True Then wsb.Range(Range("AM7")).Value = "X"
'B19 contains the True/False result of CheckBox1
'AM7 contains the text (the result of a formula), X13, which is the destination I want the "X" to be placed.

End Sub

The error states "object defined error" and points to the second part of the if statement, aka......"wsb.Range (Range("AM7".....

I appreciate the follow-up.
JasonRay

[davesexcel]

Read the forum rules on how to add code tags.

[JasonRay]

Appreciate the advice.
I am such a nooby I didn't even know the box around the code was called a code tag.

Here it is again for anyone that can help;

Seems pretty straight forward, but something is glitching it up. The following is the code I tried;

Please Login or Register  to view this content.

The error states "object defined error" and points to the second part of the if statement, aka......"wsb.Range (Range("AM7".....

I appreciate the follow-up.
JasonRay

[AlphaFrog]

If you don't specify a workbook or worksheet then Range("AM7") will reference the active workbook and its active worksheet. Could your problem be the AM7 your are wanting to reference is not on the active wb or ws?

Mabe this...
If wsa.Range("BI9") = True Then wsb.Range(wsa.Range("AM7")).Value = "X"
Or wsb?

Also what is exactly in AM7 ?

[JasonRay]

AlphaFrog,
The 2nd part of the If Statement does specify a worksheet: it is the worksheet called Bank Snapshot, set as wsb.

What I am attempting in the code is; if B19 in wsa (set as Bank Statement, the active wrksht where B19 is located) = TRUE then place an "X" in the cell address as indicated in Cell AM7 (located in a different wrksht of the same workbook). In this case Cell AM7 is the result of an Excel formula [=ADDRESS(AM6,AM5,4)], resulting in "X13", which is the Cell I want the "X" placed in.

I am likely violating a fundamental code rule due to my just getting into this, but if I can figure this one out it would help tremendously in many areas of coding.

Thank you for taking the time to help.

JasonRay

[AlphaFrog]

Just to be clear, the blue is the worksheet where you want to put the X. The red is the worksheet with the ADDRESS formula. You should reference both. I guessed at the red sheet so change it to suit. Reference it even if it's the same as the blue sheet.

...Then wsb.Range(wsa.Range("AM7")).Value = "X"

[JasonRay]

Ok.
I will try that and let you know how it goes. Heading out to a meeting right now.

Thanks,
JasonRay

[JasonRay]

AlphaFrog,
It worked!

Thank you so much for the advice.

I am falling in love with this stuff.

Best regards,
JasonRay