Type Mismatch

[Frans Scheepers]

Hi all

I have the following problem.

<b>I have this code in an Excel macro:</b>

For Each cell In [KMLData!A3.A2500]
      
        pmValid = cell.Offset(0, 4)
        pmName = cell.Offset(0, 4)
        pmLongitude = cell.Offset(0, 10)
        pmLatitude = cell.Offset(0, 11)
        pmDescription = "Region: " & cell.Offset(0, 0) & (Chr(13)) & "Required BW: " & cell.Offset(0, 6) & (Chr(13)) & "Address: " & cell.Offset(0, 7) & (Chr(13)) & "Suburb: " & cell.Offset(0, 8) & (Chr(13)) & "Town: " & cell.Offset(0, 9) & (Chr(13)) & "Site Contact: " & cell.Offset(0, 13) & (Chr(13)) & "Contact Tel: " & cell.Offset(0, 14) & (Chr(13)) & "Contact Alt Tel: " & cell.Offset(0, 15) & (Chr(13)) & "CeI Tech Resp Manager: " & cell.Offset(0, 16)
        
        If pmValid = "" Then
           Exit For
        End If
        
        'Create a placemark
        outputText = [File_details!C8] & pmName & [File_details!C9] & pmLongitude & "," & pmLatitude & [File_details!C10] & pmDescription & [File_details!C11]
        Print #1 , outputText
    
    Next

<b>And changed it to this after I duplicated the required sheets and made the adjustments to the column names:</b>

For Each cell In [KMLData (Short)!A3.A2500]
      
        pmValid = cell.Offset(0, 1)
        pmName = cell.Offset(0, 1)
        pmLongitude = cell.Offset(0, 3)
        pmLatitude = cell.Offset(0, 4)
        pmDescription = "Region: " & cell.Offset(0, 0) & (Chr(13)) & "Required BW: " & cell.Offset(0, 2)
        
        If pmValid = "" Then
        Exit For
        End If
        
        'Create a placemark
        outputText = [File_details!C8] & pmName & [File_details!C9] & pmLongitude & "," & pmLatitude & [File_details!C10] & pmDescription & [File_details!C11]
        Print #1 , outputText
    
    Next

<b><i>When executing the new macro I get a Run-time error '13': Type mismatch.</b></i>

Any idea why. When I click debug it highlights this row:

For Each cell In [KMLData (Short)!A3.A2500]

I am now at my wits end with this seemingly easy fix but cannot find the problem.

 

[Frans Scheepers]

I managed to get this issue fixed. The problem was in the sheet name with the special characters.

 

[RoryA]

Enclosing the name in single quotes would work, as would using this:

For Each cell In Sheets("KMLData (Short)").Range("A3.A2500")