Error 13 Type Mismatch

[sbraun27]

Hey everyone! I am currently writing code to eliminate blanks in a table.. I have to set p = to various worksheets, but I continue to get type mismatch errors. I have tried re cataloguing them to Sheets() or Activeworkbook.Sheets(), etc. but to no avail... can anyone tell me what I am doing so very wrong?

Sub holdings()


Dim Gcell As Range
Dim i As Integer
Dim p As Integer
Dim wb As Worksheets
Dim firstAddress As Range


Set Gcell = ActiveWorkbook.Sheets("Inputs").Range("C2")
p = 1
Do Until p = 21


    If p = 1 Then Set wb = ActiveWorkbook.Sheets("NDAG")
    If p = 2 Then Set wb = ActiveWorkbook.Sheets("NDAG_results")
    If p = 3 Then Set wb = ActiveWorkbook.Sheets("NDARP")
    If p = 4 Then Set wb = ActiveWorkbook.Sheets("NDARP_results")
    If p = 5 Then Set wb = ActiveWorkbook.Sheets("NDB")
    If p = 6 Then Set wb = ActiveWorkbook.Sheets("NDB_results")
    If p = 7 Then Set wb = ActiveWorkbook.Sheets("NDCB")
    If p = 8 Then Set wb = ActiveWorkbook.Sheets("NDCB_results")
    If p = 9 Then Set wb = ActiveWorkbook.Sheets("NDCB_INC")
    If p = 10 Then Set wb = ActiveWorkbook.Sheets("NDCB_INC_results")
    If p = 11 Then Set wb = ActiveWorkbook.Sheets("NDFO")
    If p = 12 Then Set wb = ActiveWorkbook.Sheets("NDFO_results")
    If p = 13 Then Set wb = ActiveWorkbook.Sheets("NDGI")
    If p = 14 Then Set wb = ActiveWorkbook.Sheets("NDGI_results")
    If p = 15 Then Set wb = ActiveWorkbook.Sheets("NDINC")
    If p = 16 Then Set wb = ActiveWorkbook.Sheets("NDINC_results")
    If p = 17 Then Set wb = ActiveWorkbook.Sheets("NDLTG")
    If p = 18 Then Set wb = ActiveWorkbook.Sheets("NDLTG_results")
    If p = 19 Then Set wb = ActiveWorkbook.Sheets("NREAL5")
    If p = 20 Then Set wb = ActiveWorkbook.Sheets("NREAL5_results")
[code]

 

[Norie]

Not quite sure what you are trying to do but I think you might want to change this,

Dim wb As Worksheets

to this

Dim wb As Worksheet

 

[sbraun27]

Thank you! That got me through that part.. If I may, that got me to another error in another part of my code, in which I am trying to find a specific row, then copy the entire row. I am getting an error of subscript out of range or a type mismatch (depending on how I attempt to fix it) in:

With Worksheets(wb).Range("A1:A200")        
        Set c = .Find(Gcell, LookIn:=xlValues)
        firstAddress = c.Address
    End With
    
    Worksheets(wb).Range(firstAddress).Resize(1, 49).Select
    Selection.Copy
    p = p + 1

and it is highlighted at the first line.

Thanks in advance! I am fairly new to VBA, so it has been a slow process trying to find the right syntax for this language.

 

[Norie]

It should just be wb not Worksheets(wb).