Free Microsoft Excel 2013 Quick Reference

how to fix "type mismatch" error in VBA codes

hello,
I have previously posted about "Overflow" errors and after some revision, I am getting "type mismatch" error now. can anyone more experienced in vba codes take a look at my file and point out how I can fix the errors? the goal of the codes is to mimic the calculation to the right of columns L-N. any suggestion is much appreciated.


Post your answer or comment

comments powered by Disqus
Hi All
This is driving me nuts....I've been looking at this for a couple of hours and I'm spent.
I'm getting type mismatch error in the below VBA formula, on the formula line.
All the variables seem to come up as correct ........but !!
Any assistance would be appreciated.
I'm darned if I can see the error

	VB:
	
 
For i = 3 To 8 
    For r = 40 To 50 
        Cells(r, i).Formula = "=SUMIF(B23:B" & lr - 1 & "," & Chr(34) & Cells(r, 2) & Chr(34) & "," & Range(Cells(23, i),
Cells(lr - 1, i)) & ")" 
    Next r 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I'm trying to write VBA code to copy and paste special values. I'm
using lookup formula in sheet1 only the values to be copied to be
copied to sheet3 In sheet1 if the lookup condition is satisfied it
gives the value otherwise it displays #N/A.
My syntax is like this

For Each c3 In Worksheets("calculation").Range("c24:c1203")
If c3.Value <> #N/A Then
c3.Copy
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlFormats
End If
Next c3

But while executing it is giving me the error Type mismatch and
stopping the code. Is there any suggestion pl. Tell me.

Thanks and Regards

Ramana

Hi,

Here's a new one... When I try to save my file under a different filename,
it throws a type mismatch error in the change event code for a combo box on
one of my sheets. I can save the file just fine without having it throw the
error. The weirdest part of it all is that I didn't do anything with the
part of the workbook that uses that control.

I suspect that something is corrupted, but I have no idea what that
ultimately means or how to correct it.

Any ideas? Please?

....Jay

I made a macro that would copy data from two vertical collumns into a more organized table. However, the program keeps outputting a type mismatch error. The funny thing about this error is that its marking my declaration of "sub preformat" as the error. When I attempt to put breakpoints the code never advances beyond this first line. What is the problem? (When I revert back to an earlier version of the code, the problem does not exist, but as soon as I paste the remaining code, I get an error). I am absolutely baffled.

Sub preformat()
'
' preformat Macro
'

'
'   Acquire all cells that include preformat
    
    Dim currRow1 As Long
    Dim currRow2(1 To 14) As Long
    Dim N As Long
    
    ' Establish all current rows of the table sheet
    For N = 1 To 14 Then
        currRow2(N) = 2
    Next N
    
    Application.ScreenUpdating = False
    
    For currRow1 = 4 To 1989
      If Sheet1.Range("A" & currRow1).Value = "Preformat Group" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("A" & currRow2)
            currRow2(1) = currRow2(1) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Preformat Code" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("B" & currRow2)
            currRow2(2) = currRow2(2) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Preformat Type" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("C" & currRow2)
            currRow2(3) = currRow2(3) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary is" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("D" & currRow2)
            currRow2(4) = currRow2(4) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Account or Other ID" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("E" & currRow2)
            currRow2(5) = currRow2(5) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Country Code" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("F" & currRow2)
            currRow2(6) = currRow2(6) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Routing Code" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("G" & currRow2)
            currRow2(7) = currRow2(7) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Address" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("H" & currRow2)
            currRow2(8) = currRow2(8) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Address" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("I" & currRow2)
            currRow2(9) = currRow2(9) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Account or Other ID Type" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("J" & currRow2)
            currRow2(10) = currRow2(10) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Routing Method" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("K" & currRow2)
            currRow2(11) = currRow2(11) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Name" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("L" & currRow2)
            currRow2(12) = currRow2(12) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Bank Country Name" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("M" & currRow2)
            currRow2(13) = currRow2(13) + 1
      End If
      
      If Sheet1.Range("A" & currRow1).Value = "Beneficiary Name" Then
            Sheet1.Range("B" & currRow1).Copy Destination:=Sheet2.Range("N" & currRow2)
            currRow2(14) = currRow2(14) + 1
      End If
    Next currRow1
    
End Sub


Hi all,

Would someone please help me the syntax of how to write the below function in VBA code:

=if(SUM('SHEET1'!P12:P25)>0,SUM('SHEET1'!P12:P25/SUM('SHEET1'!N12:N25)+SUM('SHEET1'!P12:P25),0)

I tried this but doesn't work:

Cells(iDy, iDx).Formula = "=if(sum(sheets("Sheet1").Range(Sheets("Sheet1").Cells(12, 16), Sheets("Sheet1").Cells(25, 16))>0, _
sum(sheets("Sheet1").Range(sheets("Sheet1").(Cells(12, 16), sheets("Sheet1").Cells(25, 16))" / " sum(Range(Cells(12, 16), Cells(25, 16))+ sum(Range(Cells(12, 14), Cells(25, 14)),0)"

cheers,
bogia

How to clear the clipboard contents in VBA using code. If I do a WORKSHEET(anyname).Copy it puts the contents in the clipboard and then paste is fine. Now how would I clear out clipboard?

Thanks

I have a type of file on many different computers but when I open it on one specific computer I cant get the function WORKDAY() to work.

I gave me a long formula C://.........WORKDAY() i then deleted the directory and put WORKDAY() back in on its own but now I get a NAME# error.

When I start the file it says the link is broken then I go into the "fix link" and I get "ATPVBAEN.XLA undefines or non rectangular name"

Does anybody know how to fix this?

Please let me know.

Thanks,
- Lior

I get this error anytime I try to delete the contents of any cell (by clicking the DELETE key...Backspace works OK) OR if I try to the FORMAT->CLEAR CONTENTS option in any cell in columns D or P.

I do have a VBA code setup that involves those cells in those columns. Any idea how to fix this problem?

Here's the code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurrRow As Long
    
    CurrRow = Target.Row
    
If Target.Column = 4 Then
    If Target.Value = "" Then
            If Cells(CurrRow, 2) = 408 Then Cells(CurrRow, 2) = 43
            If Cells(CurrRow, 2) = 406 Then Cells(CurrRow, 2) = 398
    ElseIf Target.Value  3030 And Cells(CurrRow, 2) = 408 Then Cells(CurrRow, 2) = 43
    ElseIf Target.Value  8500 And Cells(CurrRow, 2) = 406 Then Cells(CurrRow, 2) = 398
            End If
ElseIf Target.Column = 16 Then
    If Target.Value = "" Then
            If Cells(CurrRow, 14) = 408 Then Cells(CurrRow, 14) = 43
            If Cells(CurrRow, 14) = 406 Then Cells(CurrRow, 14) = 398
    ElseIf Target.Value  3030 And Cells(CurrRow, 14) = 408 Then Cells(CurrRow, 14) = 43
    ElseIf Target.Value  8500 And Cells(CurrRow, 14) = 406 Then Cells(CurrRow, 14) = 398
            End If
        End If
End Sub


I get this error anytime I try to delete the contents of any cell (by clicking the DELETE key. Backspace works OK.) OR if I try to CLEAR CONTENTS in any cell in columns D or P.

I do have a VBA code setup that involves those cells in those columns. Any idea how to fix this problem?

Here's the code:

Private Sub Worksheet_Change(ByVal
Target As Range)
    Dim CurrRow As Long
    
    CurrRow = Target.Row
    
If Target.Column = 4 Then
    If Target.Value = "" Then
            If Cells(CurrRow, 2) = 408 Then Cells(CurrRow, 2) = 43
            If Cells(CurrRow, 2) = 406 Then Cells(CurrRow, 2) = 398
    ElseIf Target.Value <= 3030 And Cells(CurrRow, 2) = 43 Then Cells(CurrRow, 2) = 408
    ElseIf Target.Value > 3030 And Cells(CurrRow, 2) = 408 Then Cells(CurrRow, 2) = 43
    ElseIf Target.Value <= 8500 And Cells(CurrRow, 2) = 398 Then Cells(CurrRow, 2) = 406
    ElseIf Target.Value > 8500 And Cells(CurrRow, 2) = 406 Then Cells(CurrRow, 2) = 398
            End If
ElseIf Target.Column = 16 Then
    If Target.Value = "" Then
            If Cells(CurrRow, 14) = 408 Then Cells(CurrRow, 14) = 43
            If Cells(CurrRow, 14) = 406 Then Cells(CurrRow, 14) = 398
    ElseIf Target.Value <= 3030 And Cells(CurrRow, 14) = 43 Then Cells(CurrRow, 14) = 408
    ElseIf Target.Value > 3030 And Cells(CurrRow, 14) = 408 Then Cells(CurrRow, 14) = 43
    ElseIf Target.Value <= 8500 And Cells(CurrRow, 14) = 398 Then Cells(CurrRow, 14) = 406
    ElseIf Target.Value > 8500 And Cells(CurrRow, 14) = 406 Then Cells(CurrRow, 14) = 398
            End If
        End If
End Sub


Hi,

Can anyone help me out with my code. Everytime I run it this error message comes up "Type mismatch". The problem seems to be in the following line:

Set Prodstyear = wsyemen.Cells.Find(styear.Value,
LookIn:=wsyemen.Range(yearsgross, yearsgross.Offset(, 30)), lookat:=xlWhole)
yearsgross is a range


I have the following code below and for some reason I cant figuire out, I am getting a type mismatch error in the "ReDim Preserve" porton of the code. I am hoping a fresh pair of eyes can help me figuire this error out.


	VB:
	
 Get145Report() 
    Sheets("Recap Report").Select 
    Dim V As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet 
    Dim bk1 As Workbook, sh1 As Worksheet 
    Dim sn As String, sm As String, sl As String, i As Long 
    Dim rng1 As Range, rng As Range 
    Dim rng2 As Range 
    Dim FirstCell As Range 
    Dim LastCell As Range 
    Dim xCount As Long 
    Set bk = Workbooks("Recaps09-2.xls") 
    Set sh = bk.Worksheets("Data") 
    Set ws = bk.Worksheets("Recap Report") 
    sn = LCase(sh.Range("D3").Value) 
    sm = sh.Range("D4").Value 
    sl = sh.Range("D5").Value 
    If sn = "all" Then 
        Set FirstCell = sh.Range("B3") 
        Set LastCell = sh.Range("B" & Rows.Count).End(xlUp) 
        For Each cell In Range(FirstCell, LastCell) 
            If cell.Value = "X" Then 
                Redim Preserve V(xCount) 
                V(xCount) = cell.Offset(0, -1).Value & " " & sl & ".xls" 
                xCount = xCount + 1 
            End If 
        Next 
        If xCount = 0 Then Exit Sub 
         'V = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")
    Else 
        V = Array(sn & " " & sl) 
    End If 
    For i = LBound(V) To UBound(V) 
        Set bk1 = Workbooks.Open("C:Service Recaps" & V(i)) 
        Set sh1 = bk1.Worksheets(sm) 
        If i = LBound(V) Then 
            Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1).End(xlUp)) 
            rng1.EntireRow.Delete 
            Set rng = ws.Range("A7") 
        Else 
            Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2) 
        End If 
        Set rng2 = sh1.Range(sh1.Range("A9"), _ 
        sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow 
        rng2.Copy Destination:=rng 
        bk1.Close SaveChanges:=False 
    Next 
     'LastRow = Range("A65536").End(xlUp).Row
    Application.Run "SortReport" 
    Application.Run "Addtotals" 
    Sheets("95_vs_145").Select 
    Application.Run "No_Customer" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've been getting a type mismatch error with my code (upon using multiple cells) and I can't quite figure out why. I have the following code:


	VB:
	
 Quicky() 
    If Worksheets("Daily").Range("B13").Value = 4 Then 
        Range("B13:H13").Merge 
        Range("B13") = "Due " & Range("B13").Value & " times" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

This code works just fine, but if I change Range("B13") in the first line to Range("B11:B13") that's when I get a type mismatch error. I don't know why. (I'm new to this) Any suggestions?

Also, I'd like to be able to make it not have to use the value 4, but use whatever value is in the range, but that's a secondary issue. I hope that is not too close to a second question as I am attempting to follow the rules.

This was also posted at http://www.geekstogo.com/forum/index...owtopic=151126

I have a Master Workbook, which has VBA to save as someone where else with a different name.

But I need to add something to the VBA code that clears out the data, based on the workbook's name.

Basically on start-up, IF the workbook's name is "Days to Invoice", then run CLEARALL Macro..

If the name is anything other than "Days to Invoice" I need it to be read-only.

Which leads me to another question..
How do I save a file in VBA to read-only?

2 questions in one post, woohoo

I worked a while on getting a macro to do what I needed in VBA. I am sure it is not the most efficient way of doing it, but its all I know. The only problem I am now encountering is getting an "Overflow" error and I can not fix it. If someone could point out what I am missing I would greatly appreciate it.

CODE:

Code:
Sub CodeRateChange()
'Keyboard Shortcut: Ctrl+t

'Vlookup of PC to get PG
Sheets("CODE").Select
For I = 1 To 1000
If Left(Cells(I, 1), 1) = "P" Then
Cells(I, 2) = Application.WorksheetFunction.VLookup(Cells(I, 1), Range("Hierarchy"), 2, False)
End If
Next I
'Calculate Base Sales Dollars
Sheets("CODE").Select
For I = 1 To 1000
If Left(Cells(I, 1), 1) = "P" Then
Cells(I, 39) = (Cells(I, 3) * Cells(I, 15))
Cells(I, 40) = (Cells(I, 4) * Cells(I, 16))
Cells(I, 41) = (Cells(I, 5) * Cells(I, 17))
Cells(I, 42) = (Cells(I, 6) * Cells(I, 18))
Cells(I, 43) = (Cells(I, 7) * Cells(I, 19))
Cells(I, 44) = (Cells(I, 8) * Cells(I, 20))
Cells(I, 45) = (Cells(I, 9) * Cells(I, 21))
Cells(I, 46) = (Cells(I, 10) * Cells(I, 22))
Cells(I, 47) = (Cells(I, 11) * Cells(I, 23))
Cells(I, 48) = (Cells(I, 12) * Cells(I, 24))
Cells(I, 49) = (Cells(I, 13) * Cells(I, 25))
Cells(I, 50) = (Cells(I, 14) * Cells(I, 26))
End If
Next I

'Calculate Price Increase Sales Dollars
Sheets("CODE").Select
For I = 1 To 1000
If Left(Cells(I, 1), 1) = "P" Then
Cells(I, 51) = (Cells(I, 3) * Cells(I, 27))
Cells(I, 52) = (Cells(I, 4) * Cells(I, 28))
Cells(I, 53) = (Cells(I, 5) * Cells(I, 29))
Cells(I, 54) = (Cells(I, 6) * Cells(I, 30))
Cells(I, 55) = (Cells(I, 7) * Cells(I, 31))
Cells(I, 56) = (Cells(I, 8) * Cells(I, 32))
Cells(I, 57) = (Cells(I, 9) * Cells(I, 33))
Cells(I, 58) = (Cells(I, 10) * Cells(I, 34))
Cells(I, 59) = (Cells(I, 11) * Cells(I, 35))
Cells(I, 60) = (Cells(I, 12) * Cells(I, 36))
Cells(I, 61) = (Cells(I, 13) * Cells(I, 37))
Cells(I, 62) = (Cells(I, 14) * Cells(I, 38))
End If
Next I

'Sum the Total Base Sales by Product Group
Sheets("BaseRateGroup").Select
For I = 3 To 1001
If Left(Cells(I, 1), 1) = "P" Then
Cells(I, 2) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseJan")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJan"))
Cells(I, 3) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseFeb")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolFeb"))
Cells(I, 4) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseMar")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolMar"))
Cells(I, 5) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseApr")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolApr"))
Cells(I, 6) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseMay")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolMay"))
Cells(I, 7) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseJun")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJun"))
Cells(I, 8) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseJul")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJul"))
Cells(I, 9) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseAug")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolAug"))
Cells(I, 10) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseSep")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolSep"))
Cells(I, 11) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseOct")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolOct"))
Cells(I, 12) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseNov")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolNov"))
Cells(I, 13) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("BaseDec")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolDec"))
End If
Next I

'Sum the Total Increase Sales by Product Group
Sheets("IncRateGroup").Select
For I = 3 To 1001
If Left(Cells(I, 1), 1) = "P" Then
Cells(I, 2) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncJan")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJan"))
Cells(I, 3) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncFeb")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolFeb"))
Cells(I, 4) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncMar")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolMar"))
Cells(I, 5) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncApr")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolApr"))
Cells(I, 6) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncMay")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolMay"))
Cells(I, 7) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncJun")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJun"))
Cells(I, 8) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncJul")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolJul"))
Cells(I, 9) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncAug")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolAug"))
Cells(I, 10) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncSep")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolSep"))
Cells(I, 11) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncOct")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolOct"))
Cells(I, 12) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncNov")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolNov"))
Cells(I, 13) = Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("IncDec")) /
Application.WorksheetFunction.SumIf(Range("GroupNumbers"), Cells(I, 1), Range("VolDec"))
End If


'Summary Page Total Volume
Sheets("Summary").Select
Cells(8, 2) = Application.WorksheetFunction.Sum(Range("VolJan"))
Cells(8, 3) = Application.WorksheetFunction.Sum(Range("VolFeb"))
Cells(8, 4) = Application.WorksheetFunction.Sum(Range("VolMar"))
Cells(8, 5) = Application.WorksheetFunction.Sum(Range("VolApr"))
Cells(8, 6) = Application.WorksheetFunction.Sum(Range("VolMay"))
Cells(8, 7) = Application.WorksheetFunction.Sum(Range("VolJun"))
Cells(8, 8) = Application.WorksheetFunction.Sum(Range("VolJul"))
Cells(8, 9) = Application.WorksheetFunction.Sum(Range("VolAug"))
Cells(8, 10) = Application.WorksheetFunction.Sum(Range("VolSep"))
Cells(8, 11) = Application.WorksheetFunction.Sum(Range("VolOct"))
Cells(8, 12) = Application.WorksheetFunction.Sum(Range("VolNov"))
Cells(8, 13) = Application.WorksheetFunction.Sum(Range("VolDec"))

'Summary Page Total Base Sales
Sheets("Summary").Select
Cells(9, 2) = Application.WorksheetFunction.Sum(Range("BaseJan"))
Cells(9, 3) = Application.WorksheetFunction.Sum(Range("BaseFeb"))
Cells(9, 4) = Application.WorksheetFunction.Sum(Range("BaseMar"))
Cells(9, 5) = Application.WorksheetFunction.Sum(Range("BaseApr"))
Cells(9, 6) = Application.WorksheetFunction.Sum(Range("BaseMay"))
Cells(9, 7) = Application.WorksheetFunction.Sum(Range("BaseJun"))
Cells(9, 8) = Application.WorksheetFunction.Sum(Range("BaseJul"))
Cells(9, 9) = Application.WorksheetFunction.Sum(Range("BaseAug"))
Cells(9, 10) = Application.WorksheetFunction.Sum(Range("BaseSep"))
Cells(9, 11) = Application.WorksheetFunction.Sum(Range("BaseOct"))
Cells(9, 12) = Application.WorksheetFunction.Sum(Range("BaseNov"))
Cells(9, 13) = Application.WorksheetFunction.Sum(Range("BaseDec"))

'Summary Page Total Sales Increase
Sheets("Summary").Select
Cells(10, 2) = Application.WorksheetFunction.Sum(Range("IncJan"))
Cells(10, 3) = Application.WorksheetFunction.Sum(Range("IncFeb"))
Cells(10, 4) = Application.WorksheetFunction.Sum(Range("IncMar"))
Cells(10, 5) = Application.WorksheetFunction.Sum(Range("IncApr"))
Cells(10, 6) = Application.WorksheetFunction.Sum(Range("IncMay"))
Cells(10, 7) = Application.WorksheetFunction.Sum(Range("IncJun"))
Cells(10, 8) = Application.WorksheetFunction.Sum(Range("IncJul"))
Cells(10, 9) = Application.WorksheetFunction.Sum(Range("IncAug"))
Cells(10, 10) = Application.WorksheetFunction.Sum(Range("IncSep"))
Cells(10, 11) = Application.WorksheetFunction.Sum(Range("IncOct"))
Cells(10, 12) = Application.WorksheetFunction.Sum(Range("IncNov"))
Cells(10, 13) = Application.WorksheetFunction.Sum(Range("IncDec"))

'Summary Page Total Sales Inc and Base
Sheets("Summary").Select
Cells(11, 2) = Cells(9, 2) + Cells(10, 2)
Cells(11, 3) = Cells(9, 3) + Cells(10, 3)
Cells(11, 4) = Cells(9, 4) + Cells(10, 4)
Cells(11, 5) = Cells(9, 5) + Cells(10, 5)
Cells(11, 6) = Cells(9, 6) + Cells(10, 6)
Cells(11, 7) = Cells(9, 7) + Cells(10, 7)
Cells(11, 8) = Cells(9, 8) + Cells(10, 8)
Cells(11, 9) = Cells(9, 9) + Cells(10, 9)
Cells(11, 10) = Cells(9, 10) + Cells(10, 10)
Cells(11, 11) = Cells(9, 11) + Cells(10, 11)
Cells(11, 12) = Cells(9, 12) + Cells(10, 12)
Cells(11, 13) = Cells(9, 13) + Cells(10, 13)

Next I
End Sub
EDIT: added Code tags - Moderator

hey all,
I am new to this forum and have been using excel since several years. I am now learning VBA and want to migrate 2003 excel files to 2007 and hope that I get answers in this forum.

Specifically, I have inherited some old excel files which had macros that performed financial calculations using function names in the formula. When I save the file to convert to 2007 I get an error message which says that I should rename the user-defined functions.

Can someone please help me as to how to rename user-defined function using VBA code?

Thanks

Deepak

I'm trying to define a named range in VBA using the following syntax.
However, it gives me an error regarding the equal sign and if I remove the
equal sign, then the named range is defined but it treats the formula as a
text value so it doesn't evaluate it. I'm sure there's something easy that
I'm overlooking but what is it?

Any help is appreciated.

ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10)'"

--
Richard

hi
pl help to debug the code it give error "type mismatch"

I am trying to use the left function in VBA code but keep receiving run-time error '438' (object doesn't support this property or method). Not sure if I can use the left function or if the syntax of the code is incorrect. Thanks.
    With wsTradeTable
        .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).ClearContents
        LastRow = Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
            
            For j = 1 To n
                If j = 1 Then
                    .Cells(i, j).Value = Application.WorksheetFunction.Left(.Range(FieldOrder(j) & i).Value, 3)
                Else
                    .Cells(i, j).Value = Range(FieldOrder(j) & i).Value
                End If
            Next j
            
        Next i
   End With


I need help with a spreadsheet. It's supposed to function like a
similar one. Links to both below.

Basically, you'll notice some shaded boxes on the worksheets. I have
people who use this form and generate receipts. Now what happens is
they fill in the 'form' and press the ENTER button. A sheet is created
everyday that copies some pertinent information over into a log. The
first sheet is generating errors and not entering information in the
right areas.

Detailed description:
When I press the ENTER button, everything else works correctly but the
information being transferred from the Cash Receipt form to the log is
starting on Row 3 instead of Row 5. Also when I tested it, it'll copy
over the first TWO receipts perfectly (at least it will copy it over to
Rows 3 and 4) but will not accept anything after that.

I'm wondering if any VBA guru in this group and see what I'm missing on
the macro contained within the spreadsheets. I just can't seem to
locate the error in the coding.

Scroll down, hit the FREE button, type in the three letter
authorization code, and hit download if you wish to download the files.
Problem - http://rapidshare.de/files/12319763/...g_H2O.zip.html
Working -
http://rapidshare.de/files/12783159/...aster.zip.html

Hi all,

I am trying to use Chip Pearson's code for extracting text files.
This is the link for it
http://www.cpearson.com/excel/imptext.htm
I like this code very much. It will allow you to specify any delimiter
character. However, I don't know how to specify a tab character in vba.
I tried "t" like in unix, perl etc. No luck. So could you please tell
me how can I specify a tab character in vba.
Thanks in advance,

shishi

​First of all - my first post! Apologies for any idiocies in the following...

I am trying to write some VB code that will allow a variable number for the row. I don't normally have an issue with this, but I keep getting a type mismatch error.
In "pidgeon code" I'm trying to Index/Match Firstname & Lastname & Leavedate in Sheet 1 with the arrays in Sheet 2. I can do this in an Excel formula, but I can't convert it to VB.
rrow is my variable for the sheet row, and leavetype is simply a string like "Sick", or "Annual Leave". I presume its a syntax issue, and I have checked that the ranges I'm searching are the same format on both sheets (e.g. Date, text, etc). I've tried quotes and brackets in different areas, I've tried Application.WorksheetFunction, I've tried pleading with the computer...! I've been at this for days!

Extract from code:

	VB:
	
 
Dim leavetype As String 
 
rrow = ActiveCell.Row 
 
 
leavetype = Evaluate("Index(Sheet1!B1:B26000, Match(Sheet2!B&rrow & Sheet2!A&rrow & Sheet2!O$4, Sheet1!S1:S26000 &
Sheet1!R1:R26000 & Sheet1!A1:A26000, 0), 0)") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Help please!

Hi, I am getting a type mismatch error with my code. The problem is that it used to run just fine and now its suddenly hitting this problem. Perhaps someone could help explain to me what's going on. Here is the important snippets...

Code:
    
Dim mtt
Dim nodays


mtt = Month(Now) 'Get the month as a number
        
        If mtt = 1 Then mtt = "January"
        If mtt = 2 Then mtt = "February"
        If mtt = 3 Then mtt = "March"
        If mtt = 4 Then mtt = "April"
        If mtt = 5 Then mtt = "May"
        If mtt = 6 Then mtt = "June"
        If mtt = 7 Then mtt = "July"
        If mtt = 8 Then mtt = "August"
        If mtt = 9 Then mtt = "September"
        If mtt = 10 Then mtt = "October"
        If mtt = 11 Then mtt = "November"
        If mtt = 12 Then mtt = "December"
'convert the number to a month name

'if the month has 31 days set nodays to 31
If mtt = "January" Or "March" Or "May" Or "July" Or "August" Or "October" Or "December" Then nodays = 31
The section of code that gives the mismatch is the final if statement. Highlighting mtt shows that it contains "August" ... The thing that is strange is that other references of mtt recognize it properly. For instance

Code:
 "August" Then
            marray(J) = "[TimeHr].[All TimeHr].[" & aYear & "].[August]"
            J = J + 1
            End If
works just fine several lines before.

Any ideas?

Greetings,

I am getting a type mismatch error on this code:
__________________________________________________ ___________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range

' On Error Resume Next ' Had to rem this out to get the error
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case vbNullString '

I need a help with the below procedure, I get a Type Mismatch Error when the
code reaches the datevalue. Please help.

Thanks,

Jin

Sub Consolidate()

Dim BaseBook As Workbook
Dim i As Integer
Dim j As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:SALES"
.SearchSubFolders = True
myStoreString = InputBox("Store Number?")
.Filename = "***" & myStoreString & "**"
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then

Set BaseBook = Workbooks.Open(.FoundFiles(1), UpdateLinks:=0)

BaseBook.Worksheets(1).Name = Left(BaseBook.Name, 3)

For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i), UpdateLinks:=0)
myFilename = myBook.Name
myBook.Worksheets(1).Move After:=BaseBook.Sheets(i - 1)
ActiveSheet.Name = Left(myFilename, 3)
Next i

For j = 1 To BaseBook.Worksheets.Count
For i = j To BaseBook.Worksheets.Count
If DateValue(Worksheets(i).Name & " 3, 2003") < _
DateValue(Worksheets(j).Name & "3, 2003") Then
Worksheets(i).Move Befo=Worksheets(j)
End If
Next i
Next j

BaseBook.SaveAs Application.GetSaveAsFilename _
("CA" & myStoreString & "sls03" & ".xls")
End If

End With

End Sub


No luck finding an answer? You could always try Google.