Substraction of cell General type value

[luolovepi]

Hi all,

I have two worksheets in different workbook, and when some conditions are satisfied, I will substract the value of Range("J" & cell.Row) in desSheet by the value of Range("J" & cell.Row) in Pr worksheet.
The variable "cell" indicates a selected cell.
The full version of code is as below:

With desSheet
        For Each cell In Range("F4", Range("F" & Rows.count).End(xlUp))
            For i = 1 To 22
                If cell.Value = Trans_Part(i) Then
                    'check whether quantity is the same
                    If .Range("J" & cell.Row).Value = Pr.Range("I" & i).Value Then
                        'delete the entire row
                        .Range("A" & cell.Row & ":N" & cell.Row).Delete
                    Else
                        'substract current quantity by the amount this row just submitted
                        .Range("J" & cell.Row).Value = CInt(.Range("J" & cell.Row).Value) - CInt(Pr.Range("I" & i).Value)
                    End If
                End If
            Next i
        Next cell
    End With

Now I receive an error message "Type mismatch" over the line:

.Range("J" & cell.Row).Value = CInt(.Range("J" & cell.Row).Value) - CInt(Pr.Range("I" & i).Value)

Could you pls help me to find out the bug and solve this problem?

Thank you very much!

yours sincerely,
lolo^^

 

[Sektor]

What is Pr?

 

[Andrew Poulsom]

You are missing some dot qualifiers here:

For Each cell In .Range("F4", .Range("F" & .Rows.count).End(xlUp))

And where have you set Pr?

 

[Weaver]

I don't think this is the problem, but you ought to avoid using words like cell for variable names - it's too close to a reserved word and can make code harder to read.

Just wondering, is cell 'cell' on sheet desSheet as well? From the code it would be whatever the currently active sheet is.

 

[luolovepi]

Thank you so much Andrew,

I have added the dot. Then I tried to run my code, in the first two times, this function has no runtime error, but it didn't substract the value successfully. Then I tried the third time, the same "type mismatch" error prompt out.
Why is that?

Best regards,
lolo

You are missing some dot qualifiers here:

For Each cell In .Range("F4", .Range("F" & .Rows.count).End(xlUp))

And where have you set Pr?

 

[luolovepi]

Hi Sektor,
Pr is a public worksheet which has been initiated on workbook open.
Best regards,
lolo

What is Pr?

 

[luolovepi]

Hi Weaver,
Thank you. I will take note of avoiding using some confusing variable name in future.
Actually I indeed lost some dot there. The cell "cell" should be in the desSheet.

Best regards,
lolo

I don't think this is the problem, but you ought to avoid using words like cell for variable names - it's too close to a reserved word and can make code harder to read.

Just wondering, is cell 'cell' on sheet desSheet as well? From the code it would be whatever the currently active sheet is.

 

[Sektor]

So, let's dissect it:

1. Red code refers to active sheet.

With desSheet
        For Each cell In [B][COLOR="Red"]Range("F4", Range("F" & Rows.count)[/COLOR][/B].End(xlUp))
        ........
        Next cell
    End With

2. This code refers to desSheet.

.Range("J" & cell.Row).Value = ...
....
.Range("A" & cell.Row & ":N" & cell.Row).Delete
...
.Range("J" & cell.Row).Value = CInt(.Range("J" & cell.Row).Value)

3. This code refers to some sheet.

Pr.Range("I" & i).Value

So, are you sure that you refer to correct sheets?

 

[luolovepi]

Re: Subtraction of cell General type value

Hi Sektor,
Actually just like what Andrew said, I need to add dots for

 For Each cell In .Range("F4", .Range("F" & .Rows.count).End(xlUp))

The full version now should be:

With desSheet
        For Each c In .Range("F4", .Range("F" & .Rows.count).End(xlUp))
            For i = 1 To 22
                If c.Value = Trans_Part(i) Then
                    'check whether quantity is the same
                    If .Range("J" & c.Row).Value = Pr.Range("I" & i).Value Then
                        'delete the entire row
                        .Range("A" & c.Row & ":N" & c.Row).Delete
                    Else
                        'subtract current quantity by the amount this row just submitted
                        .Range("J" & c.Row).Value = CInt(.Range("J" & c.Row).Value) - CInt(Pr.Range("I" & i).Value)
                    End If
                End If
            Next i
        Next c
    End With

There's still same error...

Best regards,
lolo

So, let's dissect it:

1. Red code refers to active sheet.

With desSheet
        For Each cell In [B][COLOR=red]Range("F4", Range("F" & Rows.count)[/COLOR][/B].End(xlUp))
        ........
        Next cell
    End With

2. This code refers to desSheet.

.Range("J" & cell.Row).Value = ...
....
.Range("A" & cell.Row & ":N" & cell.Row).Delete
...
.Range("J" & cell.Row).Value = CInt(.Range("J" & cell.Row).Value)

3. This code refers to some sheet.

Pr.Range("I" & i).Value

So, are you sure that you refer to correct sheets?

 

[Sektor]

Then you try to convert non-numeric data into Integer here:

...CInt(.[COLOR="Red"]Range("J" & cell.Row)[/COLOR].Value) - CInt([COLOR="red"]Pr.Range("I" & i)[/COLOR].Value)