Jan 24 2021 08:00 AM
Dear Community,
I am trying to make an order list within excel.
In the current version i use a dropdown menu (8 options) for adding products into the order list.
Up to 80 products are requested daily, which takes a lot of time to select via a dropdown menu.
Therefore i want to speed up things, by making a macro button. That adds a product always one cell lower than the previous.
Example:
Button 1 = Wash plate 1
Button 2 = Wash plate 2
First 1 click on wash plate 1 --> wash plate 1 added into list
Then i click on wash plate 2 --> wash plate 2 will be added below wash plate 1
i tried using FillDown in combination with Range. Because Range ("A1:A10"). FillDown --> fills the range A1:A10, based on the contents of cell A1. So I thought this was a good starting point. And i tried to change it by not taking cell A1 as contents but B2 and FillDown that content into range H16 to H80.
What looked like this:
Sub Wasplate 1 ()
Range ("B2", "H16":"H80")
But i can't seem to make it work. And i also don't want it to fill in the entire range from H16 to H80. But one by one as visible in the photo.
Could someone help me with this?
Thank you very much in advance!
Jan 24 2021 08:53 AM
Let's say you want to enter Wash Plate 1 or Wash Plate 2 in H16 and down.
Sub WashPlate1()
Dim r As Row
r = Range("H82").End(xlUp).Row
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("H" & r + 1).Value = "Wash Plate 1"
End If
End Sub
Sub WashPlate2()
Dim r As Row
r = Range("H82").End(xlUp).Row
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("H" & r + 1).Value = "Wash Plate 2"
End If
End Sub
Jan 24 2021 02:35 PM
@Hans Vogelaar
I get the following error: Compile error: User-defined data type not defined
And it will mark the following yellow : Dim r as Row.
If i try a different data type, like Range it will give: objectvariable or blockvariable with not defined.
So i tried the following:
Sub WashPlate1()
Dim r As Range
Set r = Range("H80").End(xlUp)
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("H" & r + 1).Value = "Wash Plate 1"
End If
End Sub
With the code above i get the "Geen ruimte meer" msg. Only nothing will be added, and there is still place left.
if i change the code to:
Sub WashPlate1()
Dim r As Range
Set r = Range("U80").End(xlUp)
If r >= 80 Then
MsgBox "Geen ruimte meer!", vbCritical
Else
Range("U" & r + 1).Value = "Wash Plate 1"
End If
End Sub
The code works, probably because column U is completely empty:
But the question now is, How can i make sure this happens within "H". And also if i click again, i will get the msg "Geen ruimte" again and i can't seem to get wash plate 1 in the cell beneath "U1".
Like this:
Wash plate 1
Wash plate 1
Could you help me further?
Jan 24 2021 03:06 PM
SolutionJan 24 2021 03:27 PM
Jan 24 2021 03:06 PM
Solution