Quick VBA Question - Adding Another Condition - Use ElseIf?

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.

Code:

---

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.

Attachment 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?

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?

Quote:

---

Originally Posted by 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?

---

Code:

---

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.

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

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

Quote:

---

Originally Posted by Aussiebear

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

Attachment 30493

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

Code:

---

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

---

Quote:

---

Originally Posted by Aussiebear

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

Code:

---

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...

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

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?

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

Code:

---

Option Explicit


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

---

Here you go Paul

Code:

---

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

---

@AussieBear - thanks

@garak0410 - say thanks to AussieBear

Quote:

---

"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

Code:

---

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

---

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

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