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.

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.

- Trying to remove Type Mismatch error in VBA sumif formula
- Type Mismatch error in VBA code
- [Q] Save As throws type mismatch error in control's code?
- Type Mismatch Error in VBA
- Syntax of how to enter function in VBA code
- How to clear the clipboard contents in VBA
- Does anyone know how to fix WORKDAY - ATPVBA.... error?
- Type Mismatch Error when deleting contents of a cell (or using CLEAR CONTENTS)
- Type Mismatch Error when deleting a cell
- "Type mismatch" error in the code
- ReDim Type Mismatch Error
- Cause For Type Mismatch Error
- How to Determine the Workbook Name in VBA
- Overflow Error in VBA Code
- NEW to VBA - How to renaming user-defined functions using VBA
- How to define a Named Range in VBA?
- Type mismatch error in form code
- How to use left function in VBA code
- Error in VBA Coding in Macro
- How to specify a tab character in excel vba...
- Using variables within Index/Match in VBA
- Strange Type Mismatch error
- Type Mismatch Error
- Help with type mismatch error

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 iIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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

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

Thanks

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

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Quicky() If Worksheets("Daily").Range("B13").Value = 4 Then Range("B13:H13").Merge Range("B13") = "Due " & Range("B13").Value & " times" End If End SubIf 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

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

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 SubEDIT: added Code tags - Moderator

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

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

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

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

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

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:Help please!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

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 = 31The 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 Ifworks just fine several lines before.

Any ideas?

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 '

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