Cell Assignment - what's wrong with this ?

[earp_]

Hi,
I have this

Worksheets("Sheet1").range("B65536").End(xlUp).Offset(1, 0) = Worksheets("Sheet2").range(pIndex & "2").Value

where pIndex = A

I've tried also

range(pIndex & "2")

but it doesn't work.
It gives me the first cell value instead of the second. Why?

 

[Andrew Poulsom]

This returned the contents of A2 when I tried it:

Sub Test()
    Const pIndex As String = "A"
    MsgBox Worksheets("Sheet2").Range(pIndex & "2").Value
End Sub

Doesn't it for you?

 

[Richard Schollar]

Hi

It works for me - it returns the value in A2 from the second sheet. Are you absolutely sure it isn't pulling back the value in A2?

 

[earp_]

this is my structure

Option Explicit
...
Dim pIndex As String

Sub mySub

if(x=1) then
pIndex = "A"
endif

if(x=2) then
pIndex = "B"
endif


Worksheets("Sheet1").range("B65536").End(xlUp).Offset(1, 0) = Worksheets("Sheet2").range(pIndex & "2").Value

End mySub

 

[Andrew Poulsom]

Your code will return the value of A2 or B2 depnding on the value of x. Doesn't it?

 

[earp_]

yes thanks and I found the error.
my row1 is hidden and now i don't know how to show it back
If I click on the second row and click unhide, it doesn't show row1

 

[Andrew Poulsom]

Click row 2 and drag up until you see 2R. Then choose Format|Row|Unhide.

 

[earp_]

thanks seems to work now.