Quick VBA Question - Adding Another Condition - Use ElseIf?

[garak0410]

I am trying to add a line of code in VBA that will ask if the range has a value of HP-1 to then to make it 16 GA. else, it needs to be 26 GA. Currently code just converts everything to 26 GA.



If Sheets "TRIM".Visible Then
Sheets("TRIM").Select
Range("D13").Select
Do Until ActiveCell.Offset(0, -1).Value =""
If ActiveCell.Value <>""Then
ActiveCell.Value ="26 GA."
End If
ActiveCell.Offset(1, 0).Select
Loop End If


So I need to add in something that will do the HP-1 condition...I did try to make an Else statement but getting NO compile errors but getting this one.

30492

(this error has nothing to do with 32bit/64 bit as we are all still using 32bit Excel with 32bit VBA code)
I'm sure it is pretty easy but can't seem to get the syntax down...
Any suggestions?

[June7]

Why is there no space in front of Then?

Test for the HP-1 value first then if field is empty.

If field is empty you want it left empty?

[garak0410]

Why is there no space in front of Then?

Test for the HP-1 value first then if field is empty.

If field is empty you want it left empty?



If Sheets("TRIM").Visible Then
Sheets("TRIM").Select
Range("D13").Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If ActiveCell.Value = "HP-1" Then
ActiveCell.Value = "16 GA."
Else
If ActiveCell.Value <> "" Then
ActiveCell.Value = "26 GA."
End If
' ActiveCell.Offset(1, 0).Select
' Loop
' End If

This is what I created and is throwing back that weird compile error.

[June7]

You have 3 multi-line If Then but only 2 End If

Options:

1. add End If before ActiveCell.Offset

2. make nested If a one-liner: If ActiveCell.Value <> "" Then ActiveCell.Value = "26 GA."

3. use ElseIf

[Aussiebear]

Are you asking if cell D13 has a value "HP-1" then change it to "G26"?

[garak0410]

Are you asking if cell D13 has a value "HP-1" then change it to "G26"?

If it has HP-1, it needs to be 16 GA. else it needs to be 26 GA.

The HP-1 is something new as we just had verything go to 26 GA.

This IF/THEN/LOOP takes place in a function...here is the full function

30493

[Aussiebear]

Sorry but I'm not following the logic of your code here.


If Sheets("Trim".Visible Then
With Range("D13")
If .Value = "HP-1" then
.Value = "16 GA"
Else
.Value = "26 GA"
End If
End If

[garak0410]

Sorry but I'm not following the logic of your code here.


If Sheets("Trim".Visible Then
With Range("D13")
If .Value = "HP-1" then
.Value = "16 GA"
Else
.Value = "26 GA"
End If
End If


The code in this project has been cobbled together for years and I just maintain it...and it has worked this way for years but let me try it this way...

[Aussiebear]

That's fine, but can you tell me why this line is in your code?


Do Until ActiveCell.Offset(0, -1).Value = ""


If all we are checking is the Range D13 and changing it value depending if its blank or "HP-1", how do we effect cell C13?

[Paul_Hossler]

I tried to copy the code to play with it, but you pasted it as a picture and I really don't want to type all that in

Use the [#] icon and paste the macro between the [CODE] ....[/CODE ] tags for readability



Option Explicit


Sub Hello()
MsgBox "Hello World!!!'"
End Sub

[Aussiebear]

Here you go Paul


Private Function AdjustmentsForSpecCond ()
If Sheets ("Purlin Girt").visible Then
Punches
Slopes
Material
End If
If Sheets ("Members).Visible Then
Sheets("Members").Select
Range ("D13").Select
Do Until ActiveCell.Offset(0,-1).Value = " "
Select Case True
Case UCase (ActiveCell.Value)= "N.A."
Activecell.Value = "Built to FL"
Case UCase (ActiveCell.Value =. "N A"
Activecell.Value = "Built to FL"
Case UCase (ActiveCell.Value) = "N/A"
Activecell.Value = "Built to FL"
Case UCase (ActiveCell.Value) = "NA"
Activecell.Value = "Built to FL"
Case (ActiveCell.Offset(1,0).Value <> " "
Range ("D3").End(xlDown).Select
Case Else
ActiveCell.OffSet(1,0).Select
End Select
Loop
End If
If Sheets ("Trim").Visible Then
Sheets("Trim").Select
Range ("D13").Select
Do Until ActiveCell.Offset(0,-1).Value = " "
If ActiveCell.Value <> " " Then
ActiveCell.Value = "26 GA."
End If
ActiveCell.Offset (1,0).Select
Loop
End If
End Function

[Paul_Hossler]

@AussieBear - thanks

@garak0410 - say thanks to AussieBear


"The code in this project has been cobbled together for years"

1. This seems more like a Sub than a Function, although either will work

2. Suggestions below are not tested, but there was a lot of redundant code and the logic was hard to follow

3. Not necessary to .Select things like worksheets or cells to work on them



Option Explicit


Private Sub AdjustmentsForSpecCond()
Dim rCell As Range

' If Sheets("Purlin Girt").Visible Then ' <<<<<<<<I assume these are subs
' Punches
' Slopes
' Material
' End If

With Worksheets("Members")

Set rCell = .Range("D13")

Do While Len(rCell.Value) > 0
Select Case UCase(rCell.Value)
Case "N.A.", "N A", "N/A", "NA"
rCell.Value = "Built to FL"
End Select

Set rCell = rCell.Offset(1, 0)

Loop
End If


' If Sheets("Trim").Visible Then
' Sheets("Trim").Select
' Range("D13").Select
' Do Until ActiveCell.Offset(0, -1).Value = " "
' If ActiveCell.Value <> " " Then
' ActiveCell.Value = "26 GA."
' End If
' ActiveCell.Offset(1, 0).Select
' Loop
' End If
End Sub

[stephanilope]

In my opinion, there should be a space in front of Then.
have HP-1, then it should be 16 GA. Otherwise it needs to be 26 GA.
slope ball (https://slopeball.io)

[Miaksol]

I think that Then should have a space in front of it.
possess HP-1, then 16 GA should be used. If not, it must be 26 GA.
run 3 (https://run3online.io)