Multiplier (VBA)

[RCPT]

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

[snb]

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]

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

[RCPT]

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]

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

[snb]

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]

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

[RCPT]

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]

Do you actually get the error with that sample file?

[georgiboy]

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

[RCPT]

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