Fill first Empty cell in column When second column has an entry inserted

[kisinana]

I posted this by mistake to MS Project
Here is my problem, I hope I explain this well. I have a value that when my macro runs it looks to see if there is a value in cell X4 if there is, it moves the value to column G and inserts it in cell G2. This works fine, problem is though column E keeps track of all inserts and when this runs last item in Column G loses item number. I need to have the block find the first empty cell in column E when X moved to G and increase the number by onefrom the block above.

My existing block

If IsEmpty(Range("X4").Value) = False Then
Range("G2").Select
Selection.Insert Shift:=xlDown
Range("X4").Select
Selection.Cut
Range("G2").Select
ActiveSheet.Paste

End If

If IsEmpty(Range("X4").Value) = True Then

End If



I have used this formula below, before in another setting and it works fine on its own. However if I try to add it in, between activesheet.paste and End if. I get an error on the last line. I need it run in the same sub as the first block as at times program runs and there is no entry in X4. Any help is appreciated.


Sub Add1toColunm()
'
' Macro2 Macro
Dim LastRow As Long
LastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
ActiveSheet.Cells(LastRow + 1, "E").Value = ActiveSheet.Cells(LastRow, "E").Value + 1


'
End Sub




Thanks Kisinana

[arnelgp]

ma
Public Function checkX4()
Dim sht As Worksheet
Set sht = Sheets(1)
With sht
If Len(.Range("X4") & "") Then
.Range("G2").Select
Selection.Insert Shift:=xlDown
.Range("X4").Select
Application.CutCopyMode = xlCut
Selection.Cut
.Range("G2").Select
.Paste

Call Add1toColunmE(sht)

End If
End With
End Function






Sub Add1toColunmE(ByRef sht As Worksheet)
'
' Macro2 Macro
Dim LastRow As Long
LastRow = sht.Range("E" & sht.Rows.Count).End(xlUp).Row
If Len(sht.Cells(LastRow, 5) & "") Then
sht.Cells(LastRow + 1, 5).Value = sht.Cells(LastRow, 5).Value + 1
Else
sht.Cells(LastRow, 5) = 1
End If
End Sub


ybe try this code:

[kisinana]

Thanks Arnelgp
but I still get
Getting the same Error 13 type mismatch on the following line:(
sht.Cells(LastRow + 1, 5).Value = sht.Cells(LastRow, 5).Value + 1
Any ideas much appreciated. I like the code for moving X over. I,ll keep trying:yes

[arnelgp]

maybe change it to:

sht.Cells(LastRow + 1, 5).Value = Val("0" & sht.Cells(LastRow, 5).Value) + 1

[kisinana]

Thanks arnelgp
Changing the line worked but with a quirk.
2 items inserts and puts in a 3
3 items skips a row and places a 1 in next cell
4 items skips a row and places a 1 in next cell
5 items inserts and puts in a 6 in the next cell
All items to 200+ works fine
Since the list will rarely have less than 40 items no problem for me.
One other thing debug now stopped on sub add1tocolumnE and I had to remove the 1 to make it run.

Thanks for all your help