Error 13 Type Mismatch

sbraun27

New Member
Joined
Aug 22, 2016
Messages
8
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?

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Not quite sure what you are trying to do but I think you might want to change this,
Code:
Dim wb As Worksheets
to this
Code:
Dim wb As Worksheet
 

sbraun27

New Member
Joined
Aug 22, 2016
Messages
8
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:

Code:
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.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
It should just be wb not Worksheets(wb).
 

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top