There is something wrong with my code and I can't seem to find it so I need another pair of eyes I think I'm going VBA Blind.
This is my code very simple if someone is doing a higher level then 3 columns are greyed out and n/a as they wont be getting any results for those modules in column D, F and G. If there not doing the higher level then it would be a reversed and they wont be getting results for modules in column B, C and E. There are 28 trainees so it would need to work for all rows.
The current code is as follows:
Private Sub Update()
Dim Course As String
Dataset = Range("A").Value
If Course = "Higher" Then
Range("D").Value = "N/A"
Range("F").Value = "N/A"
Range("G").Value = "N/A"
Range("D,F,G").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
Else
Range("B").Value = "N/A"
Range("C").Value = "N/A"
Range("E").Value = "N/A"
Range("B,C,E").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End If
End With
End Sub
However, I getting a compile error saying "Else without and If". As far as I can tell all the variables are declared the IF statement has been structured correctly.
Any thoughts?
Try this:
Private Sub Update()
Dim Course As String
Dataset = Range("A").Value
If Course = "Higher" Then
Range("D").Value = "N/A"
Range("F").Value = "N/A"
Range("G").Value = "N/A"
Range("D,F,G").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Else
Range("B").Value = "N/A"
Range("C").Value = "N/A"
Range("E").Value = "N/A"
Range("B,C,E").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End If
End Sub
If comes down to:
If ... Then
...
With ... 'Start the first With-clause
...
End With 'End the first With-clause
Else 'All With-clauses need to be closed before you can go here
...
With ... 'Start the second With-clause
...
End With 'End the second With-clause
End If 'All With-clauses needed be closed before you can go here.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments