Compile error

[salm10]

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]

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]

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]

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]

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]

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 WI pull 05_07.xlsmWI pull 05_07.xlsm

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]

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]

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]

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