If B4 =? enter 1 in A4

B

Blue

Hi

What I would like to do is

Starting at B4, if B4 has a number in it enter 1 in column A row same as B
repeat to end of column B

Thanks in advance

Blue
 
F

Frank Kabel

Hi
enter the following in A4
=IF(ISNUMBER(B4),B4,"")
and copy down for all rows
 
B

Blue

Thanks for the reply Frank

I want to do this in code so I can use it in various spreadsheet without
having to do it manually, ie Run macro ???? and it enters 1 in A if B=?

Thanks

Blue
 
F

Frank Kabel

Hi
don't know why you want a macro for this but try something like the
following
Sub change_b()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row
For RowNdx = LastRow To 4 Step -1
with Cells(RowNdx, "B")
if isnumeric(.value) then
.offset(0,-1).value = .value
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub
 
R

Ron de Bruin

Try this

Sub test()
Columns("B").SpecialCells(xlCellTypeConstants, _
xlNumbers).Offset(0, -1).Value = 1
End Sub
 
B

Blue

Frank and Ron thanks for the quick replies

Frank changed .offset(0,-1).value = .value to .offset(0,-1).value = 1
This gave the answer I wanted.

Ron your code worked great as well.

Thanks to you both

Blue
 
Top