Replace data in column H based on current cell in H and total rows in column A

[jpsanicky]

Greetings and thanks for all the help. One more here for this projects
I have worksheet that has data copied from another and columns updated with a replace function. This works great starting from A2 to the end of rows in A. However I now add data from another sheet to the end of A:900 extending the range of A to A:1800 past the initial replace. The amount of data varies with each upload. Now I need to replace blank cells in H:901 from where we left off with new data to the new end of A:1900. Example of desired results below

This code worked great to replace in column H from A2:

With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Replace What:="", Replacement:="302", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Offset(, 7).Value = "302"
End With

When I paste new data at the end of current A, say A:901 I park the cursor at the next cell in H:901

Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(8).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell

How do I replace "" with "123" from the ActiveCell H:993 position to the end of A?


[TABLE="width: 514"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]988[/TD]
[TD]Ted[/TD]
[TD]E[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]989[/TD]
[TD]Ted[/TD]
[TD]E[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]990[/TD]
[TD]Ted[/TD]
[TD]E[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]991[/TD]
[TD]Ted[/TD]
[TD]E[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]992[/TD]
[TD]Ted[/TD]
[TD]E[/TD]
[TD]Bear[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]993[/TD]
[TD]Will[/TD]
[TD]I[/TD]
[TD]Am[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]994[/TD]
[TD]Will[/TD]
[TD]I[/TD]
[TD]Am[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]995[/TD]
[TD]Will[/TD]
[TD]I[/TD]
[TD]Am[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]996[/TD]
[TD]Will[/TD]
[TD]I[/TD]
[TD]Am[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]997[/TD]
[TD]Will[/TD]
[TD]I[/TD]
[TD]Am[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

   Range(Range("H" & Rows.Count).End(xlUp).Offset(1), Range("A" & Rows.Count).End(xlUp).Offset(, 7)).Value = 123

 

[jpsanicky]

Hi Fluff
Yes that's the ticket.
All good now
Thanks for your help
JPS

 

[Fluff]

Glad to help & thanks for the feedback