PDA

View Full Version : Solved: help with this VBA



mercmannick
06-27-2006, 11:45 AM
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
06-27-2006, 12:46 PM
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:Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).Value = "K"
Cells(Rows.Count, "J").End(xlUp).Offset(1, 0).Value = "K"
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):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"Otherwise, can you explain a little better what you're trying to do?

mercmannick
06-27-2006, 12:49 PM
mvidas (http://vbaexpress.com/forum/member.php?u=289)

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
06-27-2006, 01:08 PM
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.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"

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
06-27-2006, 01:54 PM
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
06-28-2006, 06:48 AM
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: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"Matt

mercmannick
06-28-2006, 09:23 AM
mvidas (http://www.vbaexpress.com/forum/member.php?u=289)

thanx m8 that is brilliant

Merc