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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.