write range value to a cell

[cccbzg]

Hi,

I would like to write the value of a range I've selected to a particular cell. Can't seem to get that to work. Tried Valrange1 = Range("A" & s5row & ":A" & drow).Value, and then Cells(arow, 48) = Valrange1, but that only got me the first value in the range. Thought perhaps I needed DIM Valrange1 AS Range, but that caused an error.

Here's how I set up the range:

With Sheets("INPUT").Range("A" & s5row & ":A" & drow)
Set DXrng = .Find(What:=DDXit, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

Help much appreciated.

 

[Stridhan]

can get the address with, your cell = DXrng.address(0,0)

 

[cccbzg]

can get the address with, your cell = DXrng.address(0,0)

May not have explained the situation well. I want to write the value of the entire range to a cell. So for example, the range may include col A rows 3,4, and 5. Can this concatenated range be treated as one value to be output to a cell?


Sorry for the confusion. Thanks!

 

[Stridhan]

i would do something like this

Sub test()

Dim mArray As Variant, c As Variant, newVal As String
mArray = Range("A1:A5").Value

For Each c In mArray
    newVal = newVal & c & " "
Next c
Range("B1").Value = Trim(newVal)

End Sub