Solved: help with this VBA

[mercmannick]

How can i write in VBA , whatever the value in K2 is put a "K" in the next available slot in Col J, so next time when i update values in Col G the value will change in K2(by Formula) (so it will keep putting "K"'s in the next available slot on Col J

Also exactly the same for L2 and Col I

I have got this far at the moment , but not sure where to go from here

Sub KitStatus()
Dim cell As Range, Stbdrow, PortRow As Long
Dim x, y, Stbd, Port As Integer

Stbdrow = Cells(Rows.Count, 9).End(xlUp).Row
PortRow = Cells(Rows.Count, 10).End(xlUp).Row
'x = Cells(2, 11).Value 'Port
'Stbd = Cells(2, 12).Value

For x = 1 To Stbdrow
If Cells(x, 9).Value = "" Then
With Cells(x, 9).Offset(1, 0).Value = "K"

End With
End If
Next x

Many Thanks

Merc

[mvidas]

Hi Merc,

What exactly do K2 and L2 have to do with this?
If youre just trying to put a K in the next cell in I or J:[vba]Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).Value = "K"
Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).Value = "K"[/vba]
If you want a K in the first 5 cells of J, and a K in the first 3 cells of I (based on K2 and L2):[vba]If Range("K2").Value > 0 Then Range("J4").Resize(Range("K2").Value, 1).Value = "K"
If Range("L2").Value > 0 Then Range("I4").Resize(Range("L2").Value, 1).Value = "K"[/vba]Otherwise, can you explain a little better what you're trying to do?

[mercmannick]

mvidas

k2 hold a value from a formula same as l2, , what i am trying to do is put K's in col i and j based on the value in k2 and l2, next time i update sheet k2 and l2 will change , so i need it to put a K in next available cell

hope this makes sense

Merc

[mvidas]

Hi Merc,

If you are entering the Ks based on the values of K2 and L2, my second set of code above should be what you want.
What that does is look in K2. Since K2 is a 5, it will put 5 K's in column J, starting in J4.
Then it looks at L2. Since L2 is a 3, it will put 3 L's in column I starting in I4.

The only thing I might consider adding is to clear column I and J first, in case the numbers in K2 and L2 go down.[vba]Range("I4", Cells(Rows.Count, "J")).ClearContents
If Range("K2").Value > 0 Then Range("J4").Resize(Range("K2").Value, 1).Value = "K"
If Range("L2").Value > 0 Then Range("I4").Resize(Range("L2").Value, 1).Value = "K"[/vba]

However, if you only need to put a single K in the next available cell in I or J, then my first block of code above will do that.
Matt

[mercmannick]

Matt

i need to add to whatever K's are already in there , if i have 5 K's already in there and k2 changes to 7 i would need to see 12 K's in there and so on so it will allways add to what is in there

Hope that makes sense

Merc

[mvidas]

Hi Merc,

That does make more sense. Since it is a combination of the two different thoughts I first had, I will simply combine the first two sets of code I gave you:[vba]If Range("K2").Value > 0 Then Cells(Rows.Count, "J").End(xlUp) _
.Offset(1, 0).Resize(Range("K2").Value, 1).Value = "K"
If Range("L2").Value > 0 Then Cells(Rows.Count, "I").End(xlUp) _
.Offset(1, 0).Resize(Range("L2").Value, 1).Value = "K"[/vba]Matt

[mercmannick]

mvidas

thanx m8 that is brilliant

Merc