PDA

View Full Version : Compile error



salm10
07-14-2016, 10:04 AM
Hi,
I'm quite new to this so I'm struggling with putting something together and keep get a compile error saying - Compile error: Else without If

I want to reference something on one worksheet with another worksheet and if a match is found then a cell on the same row of the match is to be copied then pasted elsewhere on the row.

Example: look for a match between "B5" and "A7 to end of data in column" from 2 sheets.
When a match is found, copy from same row but different column of "B5" and paste it into the first blank cell of the same row but different column of "A7" (within a 3 cell range)
If no blanks, do nothing.
Move from "B5" to "B6" and repeat.

Code as below:
*************************************************************************** **************************
Sub Autocomp()

Dim refcit As Variant
Dim citrixm As Range
Dim citrix1 As Range
citrixm = Worksheets("Master").Range("A7").End(xlDown)
citrix1 = Worksheets("WI pull").Range("B5")
ActiveCell = citrix1

Do Until IsEmpty(citrix1) And IsEmpty(citrix1.Offset(1, 0))
refcit = Application.Match(citrix1, citrixm, 0)

If IsEmpty(Range("refcit").Offset(21, 0)) = True _
Then Range("Worksheet.citrix1").Offset(2, 0).Copy Destination:=Range("refcit").Offset(21, 0)

ElseIf IsEmpty(Range("refcit").Offset(22, 0)) = True _

Then Range("Worksheet.citrix1").Offset(2, 0).Copy Destination:=Range("refcit").Offset(22, 0)

ElseIf IsEmpty(Range("refcit").Offset(23, 0)) = True _

Then Range("Worksheet.citrix1").Offset(2, 0).Copy Destination:=Range("refcit").Offset(23, 0)

Else

citrix1.Offset(1, 0).Select
End If

Loop

*************************************************************************** **************************

I've highlighted red the sections where it gives the error

Any help greatly appreciated :)

mancubus
07-14-2016, 10:49 AM
welcome to the forum. try to construct If - End If block like this:



Sub If_Statement()

If Condition1 Then
Statement(s) 'when Condition1 is true
ElseIf Condition2 Then
Statement(s) 'when Condition2 is true
ElseIf Condition3 Then
Statement(s) 'when Condition3 is true
ElseIf Condition4 Then
Statement(s) 'when Condition4 is true
ElseIf Condition5 Then
Statement(s) 'when Condition5 is true
'...
'...
ElseIf ConditionN Then
Statement(s) 'when ConditionN is true
Else 'when all above not true
Statement(s)
End If

End Sub

mancubus
07-14-2016, 11:02 AM
If you test against single condition you can simplify the the block.


Sub If_Statement_2()
If Condition1 Then Statement 'do stuff when Condition is true, do nothing when Condition is false
End Sub


example:

Sub test_2()
temp = 5
If temp = 5 Then MsgBox "Temp is 5"
End Sub



or

Sub If_Statement_3()
If Condition1 Then Statement Else Statement 'do stuff when Condition is true, do another stauff when Condition is false
End Sub


example:

Sub test_3()
temp = 5
If temp = 5 Then MsgBox "Temp is 5" Else MsgBox "Temp is not 5"
End Sub

salm10
07-14-2016, 02:04 PM
Thanks mancubus, that's been helpful.
I'm working through other niggles with the code now so may need more help soon :)

I have found it difficult to find the right source of reading material to make sure syntax is correct and that I am structuring code properly, however I'm very eager to learn so your reply was very welcome.

mancubus
07-14-2016, 03:21 PM
you are welcome.

use code tags as explained in my signature when posting your code here.

a sample file will help helpers understand your data and sheet structure. you can post your workbook as explained in my signature.

sometimes clearly describing your requirement and posting the file rather thand posting the existing code will receive more helpful responses.

salm10
07-21-2016, 01:43 PM
Hi,

I'm still struggling with this and I've been trying to read up in what spare time I get as I figure that understanding VBA better will help.
The code has changed as I've tried various things, yet I still cannot get it to work and I've spent hours on many evenings searching for the answer. I really could do with help please.

Code is below and I have attached the file I'm working on.

I need to match the first cell with text in from column B of 'WI pull' with column A of 'Master'
Once I have a match, I need to see if the corresponding cell of column V from 'Master' is blank and if so, populate it with the score from 'WI pull'
If it's not blank, I need to look and W and then likewise at X if W is not blank also. If all 3 are not blank, it does nothing and move down 1 cell in column B of 'WI pull' before repeating.

I am getting an 'Application defined or Object defined' error when debugging and the point highlighted in red.

Hope that makes sense?

Thanks in advance :) 1669916699


Sub Autocomp()

Dim refcit As Range 'tried to set as variant, string and range but still get an error

Dim citrix1 As Range 'as above

Set citrix1 = Worksheets("WI pull").Range("B5")

ActiveCell = citrix1

'Loop'

Do Until IsEmpty(citrix1) And IsEmpty(citrix1.Offset(1, 0))

Worksheets("Master").Activate

refcit = Application.WorksheetFunction.Index(Range("Master!A7").End(xlDown), _
Application.WorksheetFunction.Match(Range("citrix1"), Range("Master!A7").End(xlDown), 0), 0) 'set refcit to compare citix id's and point to the row on master
Worksheets("Master").Activate

If IsEmpty(Range("refcit").Offset(21, 0)) Then
Worksheets("WI pull").Activate(Range("Worksheet.citrix1").Offset(2, 0)).Copy Destination:=Range("refcit").Offset(21, 0)
ElseIf Worksheets("Master").Activate(IsEmpty(Range("refcit").Offset(22, 0))) Then
Worksheets("WI pull").Activate(Range("Worksheet.citrix1").Offset(2, 0)).Copy Destination:=Range("refcit").Offset(22, 0)
ElseIf Worksheets("Master").Activate(IsEmpty(Range("refcit").Offset(23, 0))) Then
Range("Worksheet.citrix1").Offset(2, 0).Copy Destination:=Range("refcit").Offset(23, 0)

Else

citrix1.Offset(1, 0).Select

End If

Loop


End Sub

mdmackillop
07-21-2016, 02:09 PM
This seems a bit vague for testing, What should be the active cell?

ActiveCell = citrix1
This refers to cells on different sheets

.WorksheetFunction.Match(Range("citrix1"), Range("Master!A7").End(xlDown), 0), 0)
I'd suggest, set variables to refer to sheets and fully qualify ranges using these. It makes clearer coding; avoid activating sheets

Paul_Hossler
07-21-2016, 02:19 PM
This isn't exactly correct, since I was having a hard time with the macro, but maybe the change in structure will give you a nudge

I tried to simplify some things

The code should go in a Standard module, not ThisWorkbook






Option Explicit

'I want to reference something on one worksheet with another worksheet and if a match is found then a cell on the same row of the match is to be copied then pasted elsewhere on the row.
'Example: look for a match between "B5" and "A7 to end of data in column" from 2 sheets.
'When a match is found, copy from same row but different column of "B5" and paste it into the first blank cell of the same row but different column of "A7" (within a 3 cell range)
'If no blanks, do nothing.
'Move from "B5" to "B6" and repeat.

Sub Autocomp()
Dim refcit As Long
Dim citrix1 As Range
Dim wsMaster As Worksheet, wsPull As Worksheet
Dim rStart As Range, rEnd As Range, rCell As Range

Set wsMaster = Worksheets("Master")
Set wsPull = Worksheets("WI Pull")
Set rStart = wsPull.Range("B5")
Set rEnd = rStart.End(xlDown)
For Each rCell In Range(rStart, rEnd).Cells

refcit = -1
On Error Resume Next
refcit = Application.WorksheetFunction.Match(rCell.Value, wsMaster.Columns(1), 0)
On Error GoTo 0

If refcit > 0 Then
If Len(wsPull.Cells(refcit, 21).Value) = 0 Then
If Len(wsPull.Cells(rCell.Row, 22).Value) = 0 Then
wsMaster.Cells(refcit, 3).Copy wsPull.Cells(rCell.Row, 22)
ElseIf Len(wsPull.Cells(rCell.Row, 27).Value) = 0 Then
wsMaster.Cells(refcit, 3).Copy wsPull.Cells(rCell.Row, 27)
ElseIf Len(wsPull.Cells(rCell.Row, 32).Value) = 0 Then
wsMaster.Cells(refcit, 3).Copy wsPull.Cells(rCell.Row, 32)
End If
End If

End If
Next
End Sub

salm10
07-21-2016, 10:21 PM
Thanks for taking a look Paul. I am really trying with this so I really appreciate that you've taken the time to point me in the right direction. I will go away to look at getting it to work.
It's a learning curve I'm on right now and I'm finding this forum is helpful with that :)