VBA - Do a command in cell until adjacent cell is empty

[Tash Point O]

Hello -

I am trying to figure out how to copy and paste cell (for example) A1's content into cell A2, A3 and on but only if cell B2, B3 etc has content in it, otherwise, stop the copy/pasting. I also need this to be a relative reference. Please help TY so much

[/IMG]

[/IMG]

 

[offthelip]

This should do it:

Sub test()

Dim outarr As Variant
Arow1 = Cells(1, 1)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lastrow, 2))
For i = 1 To lastrow
 If colb(i, 1) = "" Then
  Exit For
 End If
Next i


ReDim outarr(1 To i - 2, 1 To 1)
For j = 1 To i - 2
 outarr(j, 1) = Arow1
Next j
Range(Cells(2, 1), Cells(i - 1, 1)) = outarr


End Sub

note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.

 

[Tash Point O]

I will try this first thing tomorrow and will let you know how it goes. Thanks so much!

 

[Tash Point O]

This should do it:

Sub test()

Dim outarr As Variant
Arow1 = Cells(1, 1)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lastrow, 2))
For i = 1 To lastrow
 If colb(i, 1) = "" Then
  Exit For
 End If
Next i


ReDim outarr(1 To i - 2, 1 To 1)
For j = 1 To i - 2
 outarr(j, 1) = Arow1
Next j
Range(Cells(2, 1), Cells(i - 1, 1)) = outarr


End Sub

note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.

Hey! This code works! However it only works for the specific area of cell 1,1. I have several groupings of this same scenario that start at for example A10, A21

 

[offthelip]

How do you plan on identifying where the next grouping starts? Do you want it to done automatically by running down column A to find the next cell with something in it? or are you going to enter the starting cell manually, either by selecting it or entering it in a message box? And what happens if the data in column B continues past the next value. Your inital requirements were very good and quite clear, your new requirements are not so clear.
The code currently just keeps going until it runs out of data in column B regardless of what is in column A. This appears to me to be what you asked for originally.

 

[Tash Point O]

^


Hello! The next grouping will be identified by the user manually UNLESS there is a way to run down Column A till it hits a number then it refers to column B whether or not to replicate A's (whichever row it is) new number. Currently The macro works wonderfully when the data set starts at A1/B1. But when I manually shift down to the next group, the macro does not run.

Let me know if I am making sense =) TY for your help with this.

[/IMG]

 

[offthelip]

try this code:

Dim outarr As Variant
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = colb(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i




Range(Cells(1, 1), Cells(lastrow, 1)) = cola

 

[Tash Point O]

^^


ALMOST!! I was trying to change your code slightly to accommodate but to no avail. What your code is doing is after the initial successful repetition of the 4 digit number in ColA (example A1:A5), the code then, for the subsequent groupings takes the first new number from ColB and replicates that in ColA, rather than taking the new number from ColA and replicating it - The 4 digit numbers in colA should be the ones replicated.

So CLOSE, I can taste it!

[/IMG]

 

[offthelip]

sorry typo:

[COLOR=#333333]Dim outarr As Variant[/COLOR]lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i



 [COLOR=#333333]Range(Cells(1, 1), Cells(lastrow, 1)) = cola[/COLOR]

 

[Tash Point O]

sorry typo:

[COLOR=#333333]Dim outarr As Variant[/COLOR]lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i



 [COLOR=#333333]Range(Cells(1, 1), Cells(lastrow, 1)) = cola[/COLOR]

Oh man, this is awesome, . I understand about 3% of your code but it's working like a charm. Thank you so much for taking the time to help me with this.