View Full Version : Quick VBA Question - Adding Another Condition - Use ElseIf?
garak0410
02-02-2023, 01:35 PM
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
02-02-2023, 02:54 PM
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
02-02-2023, 03:01 PM
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
02-02-2023, 03:07 PM
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
02-02-2023, 03:38 PM
Are you asking if cell D13 has a value "HP-1" then change it to "G26"?
garak0410
02-02-2023, 04:02 PM
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
02-02-2023, 04:21 PM
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
02-02-2023, 06:06 PM
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
02-02-2023, 07:20 PM
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
02-02-2023, 07:49 PM
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
02-02-2023, 09:46 PM
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
02-03-2023, 02:29 PM
@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
02-12-2023, 08:15 PM
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
11-06-2023, 06:59 AM
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.