Using VBA to change value in the drop down list based on set criteria

[monkeyexcel221]

Hello Everyone:

I posted a screenshot of my question below. I am new to VBA to any help will be much appreciated!!

I want to dynamically change cell C2 using VBA Code if the values on row 4 = 1. I want this to keep looping until the values on row 4 = 0.

For example, since cell E4 = 1, cell C2 changes to value in cell E2 which is 1. Since cell F4 = 1, cell C2 changes to value in F2, which is 2 and so on but stops at column H because cell H4 = 0.

Any idea on how to do this?

Thank you very much.

 

[Micron]

Too many unknowns at this point, IMO.
Always starting at the same cell in row 4? All the row data ends in column I?
I can never be >0 if H = 0?
You'll run this by calling a macro so either it matters which sheet is active, or you'll make sure the right sheet is activate?
Doesn't matter how you run it because you want the code to specify a sheet by name?
The values will always be 1 and 0?

 

[Micron]

I gave it a shot anyway since I'm trying to expand my Excel vba (much better at Access vba). I figure, just go across until you hit 0 then step back 1 column and up 2 rows and grab that value. Why worry about what's above as you go along if you only care what was in the cell above the last non zero?

Sub SetValues()
Dim varValue As Variant

Worksheets("Sheet3").Activate
Range("E4").Select
varValue = ActiveCell

Do Until varValue <> 1
   ActiveCell.Offset(0, 1).Select
   varValue = ActiveCell
Loop

Range("C2") = ActiveCell.Offset(-2, -1)

End Sub

 

[monkeyexcel221]

I gave it a shot anyway since I'm trying to expand my Excel vba (much better at Access vba). I figure, just go across until you hit 0 then step back 1 column and up 2 rows and grab that value. Why worry about what's above as you go along if you only care what was in the cell above the last non zero?

Sub SetValues()
Dim varValue As Variant

Worksheets("Sheet3").Activate
Range("E4").Select
varValue = ActiveCell

Do Until varValue <> 1
   ActiveCell.Offset(0, 1).Select
   varValue = ActiveCell
Loop

Range("C2") = ActiveCell.Offset(-2, -1)

End Sub

Thank you. What if cell F4 = 0? How do I get the code to check the column over given cell G4 = 1? Do I need to use 'Next...' code?

Yes the criteria is always either 1 or 0.

Thank you!!

 

[Micron]

What if cell F4 = 0? How do I get the code to check the column over given cell G4 = 1? Do I need to use 'Next...' code?

That was my 3rd question, although I used H and I.
In your post you mentioned 2x that it stops when it hits a zero. So if that's not the case, what exactly is the scenario? When does it stop if not at the first zero?
Perhaps you need a counter for a specific number of cells in the row.

 

[Micron]

OK, I'm off to ??️
Assuming your data will always be located from column 5 to 9 (if not, adjust accordingly). Same as before - change sheet name as required. Seems to work (note, I removed the unused and leftover declaration for worksheet (ws) as I never used it in the prior example):

Sub SetValues()
Dim varValue As Variant
Dim i As Integer

Worksheets("Sheet4").Activate
Range("E4").Select
varValue = ActiveCell

For i = 5 To 9
   If varValue = 1 Then Range("C2") = Cells(2, i)
Next

End Sub