Type mismatch error

SoraSenoie

New Member
Joined
Mar 24, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I am unsurea bout why htis is not working and giving me a type missmatch error

VBA Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,
just some quick observations

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

Rich (BB code):
'declare a variant array
   Dim ReceivedV(1 To 6) As String

- suggest try changing it to Variant

Rich (BB code):
'declare a variant array
   Dim ReceivedV(1 To 6) As Variant

on this line
Rich (BB code):
'loop through the entire array
   For Each ReceivingN In Receivingv

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

Rich (BB code):
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
 
Upvote 0
Hi,
just some quick observations

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

Rich (BB code):
'declare a variant array
   Dim ReceivedV(1 To 6) As String

- suggest try changing it to Variant

Rich (BB code):
'declare a variant array
   Dim ReceivedV(1 To 6) As Variant

on this line
Rich (BB code):
'loop through the entire array
   For Each ReceivingN In Receivingv

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

VBA Code:
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.


VBA Code:
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
 
Upvote 0
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

Rich (BB code):
'loop through the entire array
   Dim strNames(1 To 6) As Variant

Dave
 
Upvote 0
try changing this array to Variant

Rich (BB code):
'loop through the entire array
   Dim strNames(1 To 6) As Variant

Dave
so instead of -
VBA Code:
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 ?

Code:
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.
 
Upvote 0
try changing this array to Variant

Rich (BB code):
'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
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
you can populate both your arrays in a single loop

VBA Code:
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
VBA Code:
Dim i As long

affect this section?
Code:
Dim LastRow As Long

that's what I'm using to get he last cell without a value in column C and D
 
Upvote 0
you can populate both your arrays in a single loop

VBA Code:
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
VBA Code:
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 ?
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top