CODEWITHSUNDEEP
vbaexcelif-statement
carter
carter

Reputation: 75

If statement shifting cells

I currently have this If MsgBox Statement. If vbYes is selected, it will edit cell H8, if vbNo is selected, it will edit a cell 3 below that (H11). 

If MsgBox("Is X? ", vbYesNo, "Confirm") = vbYes Then
'do this to cell H8
'do this to cell h9
else
'do this to cell h11
'do this to cell h12
end if

The edits are the same - the only difference is the cell is offset 3 cells down. In this example I only use H8 and H9 but in my data there are many more cells being updated. This makes my code messy. Is there an easy way to consolidate this?

Upvotes: 1

Views: 64

Answers (2)

Cyril
Cyril

Reputation: 6829

Dim i as Long, j as string
i = 8
j = "Whatever edit is"
If MsgBox("Is X? ", vbYesNo, "Confirm") = vbYes Then
    Cells(i, "H").Value = j 'if Yes
Else
    Cells(i+3, "H").Value = j 'if No
End If

If you want to loop i for different values, this would be pretty quick.

For i = 8 to 200 Step 1
    'do soemthing using i
Next i

Upvotes: 1

newacc2240
newacc2240

Reputation: 1425

'Make a range first
Set WorkingRange = '(YourRangeHere)'
If MsgBox("Is X? ", vbYesNo, "Confirm") = vbNo Then
    Set WorkingRange = WorkingRange.Offset(3)
End If

'Do this with WorkingRange here

Upvotes: 3

Related Questions