Vba: Error type mismatch on referenced worksheet

[tatendatiffany]

i keep getting error mismatch but i have ensure the table names and columns are the same could someone please help me: here is a piece of the code

Dim Index As Sheets
Set Index = Sheets
Sheet1.Activate
Dim Element As Worksheet
Set Element = ActiveSheet
Element.Activate
SQL1 = "SELECT FROM " & Sheets("Index").range("TABLES_S").Value & Sheets("Index").range("TableClmn").Value & Sheets("Element").range("GESTATUS").Value
Debug.Print SQL1

on the higlighted line that is when i get the error

 

[Jonmo1]

My first guess is one or more of the 3 named ranges are NOT single cell ranges.

 

[tatendatiffany]

no ranges are the ranges in the worksheet e.g. TABLES_S it is C2:C39 and i named that range, so its not a single cell..
what does it mean if it is like this should i change it to something else.

 

[Jonmo1]

Well, which of the 38 values in the range TABLES_S do you want used in the string you are building? The one in C2, or C3, or C4...etc.. ?

 

[tatendatiffany]

the range c2,c3 etc each of the cell contains a different table name and i want string to look at each of them individually and loop through same as with TableClmn....Do you think I should change my approach to looping through each of the cells.


Thank you again

 

[Jonmo1]

You haven't shown any method of looping at all....

Perhaps something like this..

Dim i As Long
With Sheets("Index")
    For i = 1 to .range("TABLES_S").Rows.Count
        SQL1 = "SELECT FROM " & .range("TABLES_S")(i,1) & .range("TableClmn")(i,1) & Sheets("Element").range("GESTATUS")(i,1)
        Debug.Print SQL1
    Next i
End With

 

[tatendatiffany]

Hi,


Thank you for your help it seems to kind of be working but now i am getting subscript out of range with the line

SQL1 = "SELECT FROM " & .range("TABLES_S")(i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)

and when i hover or it SQL is empty "".

I know what the error mean but i am checking all the spelling and referenced sheets and range match up.

 

[tatendatiffany]

I checked again and realised and had missed something out. nevermind

can i ask another question with this loop i am trying to run through columns the column name and table name in the ranges i mention.
So how to i concate it so it continually loops?? do i add an Cstr + 1

I am new to VBA

Thank you