View Full Version : [SOLVED:] Multiplier (VBA)
Hello,
I am a newbie at VBA and I'm having trouble with a piece of code. The blue encircled line returns a "Run-time error '13': Type mismatch". Anyone can help?
29553
Thank you,
Rute
You'd better post a sample file with anonymous data.
You should use arrays in this case to reduce the amount of interaction (writing) to the destination sheet.
I don't think it is wise to leave row 3 empty in the destination sheet.
Paul_Hossler
03-28-2022, 05:39 AM
1. What snb said
2. The variable 'multiplier' is a Long. Since you're getting a Type Mismatch Error, I'm guessing that you're trying to assign a non-numeric value from the worksheet
That's as far as my crystal ball can go
29554
You'd better post a sample file with anonymous data.
Attached
You should use arrays in this case to reduce the amount of interaction (writing) to the destination sheet.
I don't think it is wise to leave row 3 empty in the destination sheet.
I don't see what you mean, because row3 in the destination sheet is a header.
The code:
Sub testcopydata()
Dim wsc As Worksheet 'worksheet copy
Dim wsd As Worksheet 'worksheet destination
Dim lrow As Long 'last row of worksheet copy
Dim crow As Long 'copy row
Dim drow As Long 'destination row
Dim multiplier As Long
Dim i As Long 'counting variable for the multiplier
Set wsc = Sheets("Sheet1")
Set wsd = Sheets("sheet2")
lrow = wsc.Range("b" & wsc.Rows.Count).End(xlUp).Row
drow = 2
crow = 2
With wsc
For crow = 2 To lrow 'starts at 2 because of the header row
multiplier = wsc.Cells(crow, 2).Value 'copies the value in column b
For i = 1 To multiplier
wsd.Cells(drow, 1).Value = .Cells(crow, 1).Value
wsd.Cells(drow, 2).Value = .Cells(crow, 2).Value
wsd.Cells(drow, 3).Value = .Cells(crow, 4).Value
wsd.Cells(drow, 4).Value = .Cells(crow, 5).Value
wsd.Cells(drow, 5).Value = .Cells(crow, 6).Value
wsd.Cells(drow, 6).Value = .Cells(crow, 7).Value
drow = drow + 1 'increasing the row in worksheet destination
Next i
Next crow
End With
End Sub
So I want columns in sheet 1 to be copied to sheet 2 but replicated the number of times in column 2 (planned #) of sheet1 if column 3 is empty. I wrote a separate code for when I want to use column 3 (actual #), and then another module with an IF that uses the actuals in case there are actual data. In case there are no actual data, stick to planned data.
But I'm not having problems with the other modules only with the multiplier line in module 1.
georgiboy
03-28-2022, 07:06 AM
The only way I can recreate the error is by putting a letter in column B - for this reason I am fairly certain that the values in column B are not being viewed by excel as numbers. What we need to do is find out why?
As the code seems to be fine our end the best thing to do is upload a file with the error contained.
As 'crow' increments there should be no text or (numbers and text) in column B
Instead of your code I'd use:
Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
For j = 2 To UBound(sn)
c00 = c00 & Replace(Space(sn(j, 2)), " ", " " & j)
Next
sp = Application.Transpose(Split(1 & c00))
Sheet2.Cells(1).Resize(UBound(sp), 6) = Application.Index(sn, sp, Array(1, 2, 4, 5, 6, 7))
End Sub
Paul_Hossler
03-28-2022, 08:35 AM
I don't see what you mean, because row3 in the destination sheet is a header.
So I want columns in sheet 1 to be copied to sheet 2 but replicated the number of times in column 2 (planned #) of sheet1 if column 3 is empty. I wrote a separate code for when I want to use column 3 (actual #), and then another module with an IF that uses the actuals in case there are actual data. In case there are no actual data, stick to planned data.
But I'm not having problems with the other modules only with the multiplier line in module 1.
It seems to run OK for me
Possibly your workbook is corrupted - try the attached WB that I re-created and see if there's still the problem
29555
Thank you for your help.
I still receive the same error.
I am sending a replica of the file I'm using, to see if you can help.
I have searched for non-numeric data in the column I am using for the multiplier and can't find any.
Cheers,
Rute
Aflatoon
03-29-2022, 02:55 AM
Do you actually get the error with that sample file?
georgiboy
03-29-2022, 03:28 AM
Couple of things:
lrow = wsc.Range("b" & wsc.Rows.Count).End(xlUp).Row
Above returns the end of the table and not the end of the data within the table - this means the code is running to the end of the table. The problem with this is that cell B14 is blank, this is where you get the error because you are trying to define a long from a blank cell.
If you reduce the table size to the last row of data in 'CopyWorksheet' it should run through fine. Either that or you need a number to be present in column B all the way down.
The error in setting multiplier happens when crow is 14 which is the first blank in column B
You could look to define lrow as below:
lrow = wsc.ListObjects("Table4").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Giving us the below:
Sub CopyData2()
Dim wsc As Worksheet 'worksheet copy
Dim wsd As Worksheet 'worksheet destination
Dim lrow As Long 'last row of worksheet copy
Dim crow As Long 'copy row
Dim drow As Long 'destination row
Dim multiplier As Long
Dim i As Long 'counting variable for the multiplier
Set wsc = Sheets("CopyWorksheet")
Set wsd = Sheets("DestinationWorksheet")
lrow = wsc.ListObjects("Table4").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
drow = 4
crow = 2
With wsc
For crow = 2 To lrow 'starts at 2 because of the header row
multiplier = .Cells(crow, 2).Value 'copies the value in column b
For i = 1 To multiplier
wsd.Cells(drow, 11).Value = .Cells(crow, 1).Value
wsd.Cells(drow, 12).Value = .Cells(crow, 2).Value
wsd.Cells(drow, 17).Value = .Cells(crow, 4).Value
wsd.Cells(drow, 13).Value = .Cells(crow, 5).Value
wsd.Cells(drow, 14).Value = .Cells(crow, 6).Value
wsd.Cells(drow, 21).Value = .Cells(crow, 7).Value
drow = drow + 1 'increasing the row in worksheet destination
Next i
Next crow
End With
End Sub
Hope this helps
Hi! Thank you so much. It's sorted. I was using a IFERROR to return "" and thatīs what was creating the type mismatch. Thank you for your trouble, and for the code suggestions. I will certainly use them.
Regards,
Rute Teixeira
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.