Previous Topic Next Topic

Assign value from one cell to other-Excel VB

Closed
LvD - Oct 27, 2011 at 07:27 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 1, 2011 at 10:01 AM
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
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 1, 2011 at 10:01 AM
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
0

Similar discussions

Formula to subtract one cell value from another.
autigers71 -
vcoolio -

1 response
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses