Using of Indirect in Excel VBA, I think...

[Emperor]

Hi all,

I have, hopefuly, a simple question;

In a string I have the column letter in which the data needs to come

So I need something like;

dim ws as Worksheets
dim ws2 as Worksheets
dim StartCLM as string

set ws = Worksheets("Sheet1")
set ws2 = Worksheets("Sheet2")

StartCLM = ws.range("A1").value
'Now StartCLM is "D" (in text)

ws2.range(StartCLM & "2").value = ws.range("A1").value
'So I would want to change the value of WS2.range("D2")

I think I need to use Indirect but I wouldnt know how.
Any help will be welcome!

Mathijs.

 

[Jonmo1]

What you have should work, but you should change

Dim ws As Worksheets
Dim ws2 As Worksheets

to just

Dim ws As Worksheet
Dim ws2 As Worksheet

 

[CharlesChuckieCharles]

You don't need indirect in code

JONMO is right you've used the wrong object

dim ws as Worksheet
dim ws2 as Worksheet
dim StartCLM as string
 
set ws = Worksheets("Sheet1")
set ws2 = Worksheets("Sheet2")
 
StartCLM = ws.range("A1").value
'Now StartCLM is "D" (in text)
 
ws2.range(StartCLM & "2").value = ws.range("A1").value
'So I would want to change the value of WS2.range("D2")
[COLOR=red]''' At this point the value of D2 = "D"[/COLOR]

What are you trying to achieve?

 

[Emperor]

I wan't to use the value of cell A1 in a range.
The value is a "D"

So I want to set a range to "D2", problem is I can't set it like this.
I am getting an error on "ws2.range(StartCLM & "2").value"
a method range faillure....

ps. You and Jonmo are right about the Worksheet, I just made a typo in the post, sorry

 

[Jonmo1]

StartCLM = ws.range("A1").value

Are you sure Sheet1 A1 = D ???

At the time you get the error, and you click Debug
Hover your mouse over the StartCLM variable...
It will popup a window displaying it's current value.

 

[CharlesChuckieCharles]

The code you posted works fine ... paste the actual code from your module

 

[jim may]

Do you mean something like this?

Sub Test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim rg As Range
Dim StartCLM As String
 
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
 
StartCLM = ws.Range("A1").Value
'Now StartCLM is "D" (in text)
 
Set rg = ws2.Range(StartCLM & 2)
'So I would want to change the value of WS2.range("D2")
''' At this point the value of D2 = "D"

End Sub

 

[Emperor]

Thank all of you for the replies.
I have got it working (there was a little flaw), sort of.

Maybe you can help me with this;
the data i'm copying is in a range:
ws2.Range("B8:J8")

The place where I am pasting it starts at D2, but when I only define D2 in my code, it doesnt fill E2, F2, etc
So how can I paste a "range" to a "range starting at D2" ?

 

[CharlesChuckieCharles]

Try selecting D2 once you got the address

then ActiveSheet.Paste

 

[Jonmo1]

Try

ws2.range(StartCLM & "2").Resize(1,9).value = ws2.Range("B8:J8")
.value