Help with loop function

grd3040

New Member
Joined
Sep 22, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon All
This is my first post on the forum so please excuse me if my descriptions are not exact in the beginning!

I have a table with 4 columns. I need to write code that will set off changes to columns 2,3 & 4 when data in column 1 is changed and this must work by row i.e. only to make these changes to row 1 when the data in the cell in column 1 row 1 is changed but I need the code to work in every row of the table without having to write separate code for each line. I think loop function may be the answer but I'm having trouble identifying the right vba code to reference the cells in the table the way I want and then applying the right loop function. My table is dynamic in rows and not columns but guessing loop function takes this into account.

I have spent a great deal of time researching this but can't seem to get the start I need so any help will be very much appreciated.

Thanks in advance
G
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
What changes to columns 2,3 & 4 do you want to make? Please describe in detail.
 
Upvote 0
What changes to columns 2,3 & 4 do you want to make? Please describe in detail.
Hi mumps
Thanks for getting back to me.
In column 1 a drop down list allows selection of an order item i.e. bottle, case, keg, pack
If I select bottle for example then I want my code to change column 2, 3 & 4 to either text n/a if that column doesn't apply or if it does, to change the data validation address to a specific table with further selection data applicable to bottle. I have posted the code that I have written but it only works on the specified row currently where I would like it to apply to any row in the table without having to repeat this code for every row of the table.

I hope this helps & thanks so much for your help!

Regards
G

Sub aaSelect()
' BOTTLE

Application.ScreenUpdating = False
If Range("F5") = "Bottle" Then
Range("G5:J5").Select
Selection.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size1[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure1[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size1[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("F5").Select
End If

' CASE
If Range("F5") = "Case" Then

Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Case_Size3[Case Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size3[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure3[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size3[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Range("F5").Select
Application.ScreenUpdating = False

' EACH
Application.ScreenUpdating = False
If Range("F5") = "Each" Then
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
ActiveCell.FormulaR1C1 = "1"
Application.ScreenUpdating = False
Range("I5").Select
ActiveCell.FormulaR1C1 = "Each"
Application.ScreenUpdating = False
Range("J5").Select
ActiveCell.FormulaR1C1 = "Each"
Range("F5").Select
End If

' KEG
If Range("F5") = "Keg" Then
Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
Range("G5:J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size4[Unit Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Measure4[Measure]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("J5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size4[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Range("F5").Select

' PACK
If Range("F5") = "Pack" Then
Application.ScreenUpdating = False
Range("G5:J5").Select
Selection.ClearContents
Application.ScreenUpdating = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("G5").Select
ActiveCell.FormulaR1C1 = "n/a"
Application.ScreenUpdating = False
Range("H5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Unit_Size6[Pack Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Application.ScreenUpdating = False
Range("I5").Select
ActiveCell.FormulaR1C1 = ""
Application.ScreenUpdating = False
Range("I5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(""Serve_Size6[Serve Size]"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Application.ScreenUpdating = False
Range("J5").Select
ActiveCell.FormulaR1C1 = "Each"
Range("F5").Select
End If


Application.ScreenUpdating = True
End Sub
 
Upvote 0
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top