Macro to autofill a number in empty cells in a column

**excelaspire0219** · 01-27-2009, 05:23 PM

Hi,
I have two issues:
1. I need a macro to find a unique number, say a 10 digit number starting with 4100.. and move it 1 row down and 3 rows to the left. It needs to look only in one column (E) for this number. This row contains several unique numbers, variable number of blank cells between them, all having 10 digits and starting with 4100.. - So the macro needs to repeat this for every unique number.

2)Once it does this, I need it to autofill the unique number in all the cells in column A until the next unique number is reached.

Can anyone help me with this please?

Thanks.

**PetrH** · 01-28-2009, 02:06 AM

Hi, it would be very helpful if you attache the example file. It shall be easy job. I havn't understood exactely.
Do you want to search for one specific number and move it or all not empty cells in column E shall be moved?
Petr

**excelaspire0219** · 01-28-2009, 10:19 AM

Hi,
Here is the sample, sheet 1 is how the file looks and sheet 2 is how I would it to look (the number in blue font should be deleted) as you can see the number in blue font moved one row down and one row to the left, and got autofilled. It is ok if the number autofills until it reaches the next number, that way I can write a code to delete the rows in which certain cells are blank.

Hope this helps.

**PetrH** · 01-29-2009, 03:31 AM

Hi, try this and let me know if it functions. Regards. Petr

Sub autofill()
Dim Cell As Range
Dim Num As String

Set Cell = Range("B1")
Do Until Cell = "END OF REPORT"
Select Case Left(Cell.Value, 3)
Case "420"
Num = Cell.Value
Cell.Value = ""
Case Is <> ""
Cell.Offset(0, -1).Value = Num
End Select
Set Cell = Cell.Offset(1, 0)
Loop
End Sub

**VBA Noob** · 01-29-2009, 03:42 AM

PetrH,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here