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?

VBA_multiplier.jpg
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]

VBA_test.xlsm

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

Capture.JPG

[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