Type mismatch error

[SoraSenoie]

I am unsurea bout why htis is not working and giving me a type missmatch error

Private Sub SubmitCSR_Click()
 Dim LastRow As Long
    Dim ws As Worksheet: Set ws = Sheets("GP count")
     'declare a variant array
   Dim ReceivedV(1 To 6) As String

   'populate the array
   ReceivedV(1) = R1.Value
   ReceivedV(2) = R2.Value
   ReceivedV(3) = R3.Value
   ReceivedV(4) = R4.Value
   ReceivedV(5) = R5.Value
   ReceivedV(6) = R6.Value

   'declare a variant to hold the array element
   Dim ReceivingN As Variant

   'loop through the entire array
   Dim strNames(1 To 6) As String

   'populate the array
   strNames(1) = ToggleButton1.Value
   strNames(2) = ToggleButton2.Value
   strNames(3) = ToggleButton3.Value
   strNames(4) = ToggleButton4.Value
   strNames(5) = ToggleButton5.Value
   strNames(6) = ToggleButton6.Value

   'declare a variant to hold the array element
   Dim StockC As Variant

   'loop through the entire array
   For Each ReceivingN In Receivingv
   For Each StockC In strNames
    If StockC = True Then
LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Range("C" & LastRow + 1).Value = StockC
LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
ws.Range("E" & LastRow + 1).Value = ReceivingN
   End If
Next StockC
Next ReceivingN


End Sub

 

[dmt32]

Hi,
just some quick observations

your comment states that you are declaring a Variant Array yet you have declared it as String?

'declare a variant array
   Dim ReceivedV(1 To 6) As String

- suggest try changing it to Variant

'declare a variant array
   Dim ReceivedV(1 To 6) As Variant

on this line

'loop through the entire array
   For Each ReceivingN In Receivingv

looks like you have a typo & should I suspect, be

For Each ReceivingN In ReceivedV

If you place Option Explicit at top of the code page, the compiler would pick such errors this up for you.

As said, I have only made quick glance but hopefully, suggestions will help resolve your issue

Dave

 

[SoraSenoie]

Hi,
just some quick observations

your comment states that you are declaring a Variant Array yet you have declared it as String?

'declare a variant array
   Dim ReceivedV(1 To 6) As String

- suggest try changing it to Variant

'declare a variant array
   Dim ReceivedV(1 To 6) As Variant

on this line

'loop through the entire array
   For Each ReceivingN In Receivingv

looks like you have a typo & should I suspect, be

For Each ReceivingN In [B]ReceivedV[/B]

If you place Option Explicit at top of the code page, the compiler would pick such errors this up for you.

As said, I have only made quick glance but hopefully, suggestions will help resolve your issue

Dave

Yep I noticed this and fixed it and its working...but not im having an issue that its passing everything even if the toggle button is not pressed. this is the updated version
this is my first time running loops and using arrays so any help is greatly appreciated.

Private Sub SubmitCSR_Click()
 Dim LastRow As Long
    Dim ws As Worksheet: Set ws = Sheets("GP count")
     'declare a variant array
      Dim StockCaption(1 To 6) As String
   'populate the array
   StockCaption(1) = ToggleButton6.Caption
   StockCaption(2) = ToggleButton2.Caption
   StockCaption(3) = ToggleButton4.Caption
   StockCaption(4) = ToggleButton5.Caption
   StockCaption(5) = ToggleButton1.Caption
   StockCaption(6) = ToggleButton3.Caption
   'declare a variant to hold the array element
   Dim StockCC As Variant
  
   Dim ReceivedV(1 To 6) As String
   'populate the array
   ReceivedV(1) = R1.Value
   ReceivedV(2) = R2.Value
   ReceivedV(3) = R3.Value
   ReceivedV(4) = R4.Value
   ReceivedV(5) = R5.Value
   ReceivedV(6) = R6.Value
   'declare a variant to hold the array element
   Dim ReceivingN As Variant
   'loop through the entire array
   Dim strNames(1 To 6) As String
   'populate the array
   strNames(1) = ToggleButton6.Value
   strNames(2) = ToggleButton2.Value
   strNames(3) = ToggleButton4.Value
   strNames(4) = ToggleButton5.Value
   strNames(5) = ToggleButton1.Value
   strNames(6) = ToggleButton3.Value
   'declare a variant to hold the array element
   Dim StockValue As Variant
   'loop through the entire array
   For Each StockValue In strNames
   For Each ReceivingN In ReceivedV
   For Each StockCC In StockCaption
    If StockValue = True Then
    LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Range("C" & LastRow + 1).Value = StockCC
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    ws.Range("E" & LastRow + 1).Value = ReceivingN
   End If
Next StockCC
Next ReceivingN
Next StockValue
End Sub

 

[dmt32]

im having an issue that its passing everything even if the toggle button is not pressed. this is the updated version

try changing this array to Variant

'loop through the entire array
   Dim strNames(1 To 6) As Variant

Dave

 

[SoraSenoie]

try changing this array to Variant

'loop through the entire array
   Dim strNames(1 To 6) As Variant

Dave

so instead of -

Dim strNames(1 To 6) As String
   'populate the array
   strNames(1) = ToggleButton6.Value
   strNames(2) = ToggleButton2.Value
   strNames(3) = ToggleButton4.Value
   strNames(4) = ToggleButton5.Value
   strNames(5) = ToggleButton1.Value
   strNames(6) = ToggleButton3.Value
   'declare a variant to hold the array element
   Dim StockValue As Variant

it should be like this ?

Dim strNames(1 To 6) As Variant
   'populate the array
   strNames(1) = ToggleButton6.Value
   strNames(2) = ToggleButton2.Value
   strNames(3) = ToggleButton4.Value
   strNames(4) = ToggleButton5.Value
   strNames(5) = ToggleButton1.Value
   strNames(6) = ToggleButton3.Value

is there any way to get it all into one loop instead of the 3 that I have? because its currently passing the togglebutton captions 6 times instead of just one and all of them are also being passed.

 

[SoraSenoie]

try changing this array to Variant

'loop through the entire array
   Dim strNames(1 To 6) As Variant

Dave

I seem to be getting the same results still running it 6 times and passing all the values but only for

For Each StockCC In StockCaption

 

[dmt32]

is there any way to get it all into one loop instead of the 3 that I have? because its currently passing the togglebutton captions 6 times instead of just one and all of them are also being passed.

you can populate both your arrays in a single loop

Dim i As Long
    For i = 1 To 6
        'populate the array
        ReceivedV(i) = Me.Controls("R" & i).Value
        'populate the array
        strNames(i) = Me.Controls("ToggleButton" & i).Value
    Next i

Dave

 

[SoraSenoie]

you can populate both your arrays in a single loop

Dim i As Long
    For i = 1 To 6
        'populate the array
        ReceivedV(i) = Me.Controls("R" & i).Value
        'populate the array
        strNames(i) = Me.Controls("ToggleButton" & i).Value
    Next i

Dave

wouldn't

Dim i As long

affect this section?

Dim LastRow As Long

that's what I'm using to get he last cell without a value in column C and D

 

[SoraSenoie]

you can populate both your arrays in a single loop

Dim i As Long
    For i = 1 To 6
        'populate the array
        ReceivedV(i) = Me.Controls("R" & i).Value
        'populate the array
        strNames(i) = Me.Controls("ToggleButton" & i).Value
    Next i

Dave

Private Sub SubmitCSR_Click()
 Dim LastRow As Long
 Dim i As Long
    Dim ws As Worksheet: Set ws = Sheets("GP count")
    Dim StockCaption(1 To 6) As Variant
   'populate the array
   StockCaption(1) = ToggleButton6.Caption
   StockCaption(2) = ToggleButton2.Caption
   StockCaption(3) = ToggleButton4.Caption
   StockCaption(4) = ToggleButton5.Caption
   StockCaption(5) = ToggleButton1.Caption
   StockCaption(6) = ToggleButton3.Caption
   'declare a variant to hold the array element
   Dim StockCC As Variant
   Dim ReceivedV(1 To 6) As Variant
   'populate the array
   ReceivedV(1) = R1.Value
   ReceivedV(2) = R2.Value
   ReceivedV(3) = R3.Value
   ReceivedV(4) = R4.Value
   ReceivedV(5) = R5.Value
   ReceivedV(6) = R6.Value
   'declare a variant to hold the array element
   Dim ReceivingN As Variant
   'loop through the entire array
   Dim strNames(1 To 6) As Variant
   'populate the array
   strNames(1) = ToggleButton6.Value
   strNames(2) = ToggleButton2.Value
   strNames(3) = ToggleButton4.Value
   strNames(4) = ToggleButton5.Value
   strNames(5) = ToggleButton1.Value
   strNames(6) = ToggleButton3.Value
   'declare a variant to hold the array element
   Dim StockValue As Variant
   'loop through the entire array
 
   If StockValue = True Then
     For i = 1 To 6
   ReceivedV(i) = Me.Controls("R" & i).Value
        'populate the array
        StockCaption(i) = Me.Controls("ToggleButton" & i).Caption
    LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ws.Range("C" & LastRow + 1).Value = StockCC
    LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    ws.Range("E" & LastRow + 1).Value = ReceivingN
    Next i
   End If
End Sub

would this be the correct way to add this into the code ?

 

[dmt32]

would this be the correct way to add this into the code ?

Based on code in your first post, you could populate your arrays in following manner

Option Explicit
Private Sub SubmitCSR_Click()
    Dim LastRow     As Long, i As Long
    Dim ws          As Worksheet
    
    'declare a variant to hold the array element
    Dim ReceivingN  As Variant, StockC     As Variant
    
    'declare a variant array
    Dim ReceivedV(1 To 6) As Variant, strNames(1 To 6) As Variant
    
    Set ws = ThisWorkbook.Worksheets("GP count")
    
    For i = 1 To 6
        'populate the array
        ReceivedV(i) = Me.Controls("R" & i).Value
        'populate the array
        strNames(i) = Me.Controls("ToggleButton" & i).Value
    Next i
    
    'loop through the entire array
    For Each ReceivingN In ReceivedV
        For Each StockC In strNames
            If StockC = True Then
                LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
                ws.Range("C" & LastRow + 1).Value = StockC
                LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
                ws.Range("E" & LastRow + 1).Value = ReceivingN
            End If
        Next StockC
    Next ReceivingN
    
End Sub

Above untested & is intended just as an illustration of an approach to consider that may help you with your project.

As the original question in this thread has been resolved, can I suggest that if you are continuing having difficulties with your project that you start a new post & explain to forum what it is you are trying to do & issues you are having. Including a copy of the worksheet using MrExcel Addin XL2BB - Excel Range to BBCode
is also helpful – plenty here to offer assistance.



Dave