help with VBA Type Mismatch

rcirone

Active Member
Joined
Mar 12, 2009
Messages
457
My data is on the (Cal) tab from A2:L300. Were I need it too be is on the (Dashboard) tab from U5:AF. Manager name list is on (U2) and when I press the button I get Type Mismatch please help


Code:
Sub ManagerLastWktoDashBd()

Dim c As Range
Dim lr As Long
Dim mgrCol As Range
Dim aMgr As String

aMgr = Range("U2")

With Sheets("Cal")

lr = Range("A" & Rows.Count).End(xlUp).Row

Set mgrCol = Range("A1:A" & lr)

Application.ScreenUpdating = False
 For Each c In mgrCol
   If c = aMgr And c.Offset(, 1) = c.Offset(, 6) Then
  
      c.Offset(, -2).Resize(1, 6).Copy
      Sheets("Dashboard").Range("u" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
      Sheets("Dashboard").Range("ah" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
   End If
 Next
  
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

You've defined aMgr as a String when it should be Range(?).

Or maybe you should be looking at the value in c.

hth
 
Last edited:

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Inside your With block you haven't referenced the ranges to the With object, they should start with a (.), so the parent object defaults to the Activesheet. Try changing to (untested, so there may be typos):

Code:
Sub ManagerLastWktoDashBd()

Dim c As Range
Dim lr As Long
Dim mgrCol As Range
Dim aMgr As String


With Sheets("Cal")

aMgr = .Range("U2") 'I'm guessing this is on "Cal" as well

lr = .Range("A" & Rows.Count).End(xlUp).Row

Set mgrCol = .Range("A1:A" & lr)

Application.ScreenUpdating = False
 For Each c In mgrCol
   If c = aMgr And c.Offset(, 1) = c.Offset(, 6) Then
  
      c.Offset(, -2).Resize(1, 6).Copy
      Sheets("Dashboard").Range("u" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
      Sheets("Dashboard").Range("ah" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
   End If
 Next
  
End With
Application.ScreenUpdating = True
End Sub



You've defined aMgr as a String when it should be Range(?).

Ukmikeb, this will assign the value of the Range as a string to aMgr.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top