Previous Topic Next Topic

Assign value from one cell to other-Excel VB

Closed
LvD - 27 Oct 2011 à 07:27
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 1 Nov 2011 à 10:01
Hello,

I need to assign a value to a cell and to the following 5 cells. If I could assign a value to a cell from one cell to another it would look as follows:

A2 = VLOOKUP(A1,$B$1:$B$10,2)
IF(A2>0, A2&A3&A4&5=A2,0)

Thus A1-A5 would have the same value if A1 had a value. The 6th cell should do the vlookup again. I know that this is not possible with Excel but how can I do this with the help of VBA?

Regards

1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
1 Nov 2011 à 10:01
Hi LvD,

You can do this using formula's.

A1: VLOOKUP formula
A2:A5: each cell contains same formula: =IF($A$1<>"",$A$1,"")
A6: VLOOKUP formula
A7:A10: =IF($A$6<>"",$A$6,"")
etc...

Depending on how much data you have it might be easier to use macro: 
Sub test()
Dim x As Integer
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1

Do

If Cells(x, 1) <> "" Then Cells(x + 1, 1) = Cells(x, 1)
    Cells(x + 2, 1) = Cells(x, 1)
    Cells(x + 3, 1) = Cells(x, 1)
    Cells(x + 4, 1) = Cells(x, 1)

x = x + 5

Loop Until x > lRow

End Sub
This code is based on the assumption that the VLOOKUP's are already in place.

Best regards,
Trowa

Similar discussions

Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
How to extract data from one excel file to another
zamorin -
zamorin -

2 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Extract data from one sheet to another
phanikr -
Mazzaropi -

1 response