Novice Excel VBA coder - type mismatch error

[Lara1]

Hi,

I'm a total beginner to VBA, so please bear with me if I seem a bit dense.

What I'm Trying to Achieve

I'm trying to write a procedure in Excel, which is supposed to
- look at the pH values stored in column E, row by row
- compare this to a threshold value of 5
- enter a string ("pH range C") in column K, if the value in column E exceeds 5.

The Problem
I'm getting a type mismatch error, as shown in the source code below:

Sub PNEC()
 
Dim pH As Double
Dim PNEC As String
Dim usedrows As Double
Dim Row As Integer
 
usedrows = Worksheets("Fal").UsedRange.Rows.Count
pH = Worksheets("Fal").Columns("E").Value 'TYPE MISMATCH ERROR HERE
PNEC = Worksheets("Fal").Columns("K").Value
 
For Row = 2 To usedrows Step 1
 
If pH > 5 Then
PNEC = "pH range C"
End If
 
Next Row
 
End Sub
 

What I think Is Happening
In the sheet "Fal", all data in columns E and K are numeric except for the header row, which is text. I think it is this text that is causing the problem, conflicting with the "Double" variable type I've defined for pH.

My Questions

• Does my diagnosis seem reasonable, or have I misunderstood the problem entirely?

• How can I get around the problem?

Any insights gratefully received,

Lara

[MikeTheBike]

Hi,

I'm a total beginner to VBA, so please bear with me if I seem a bit dense.

What I'm Trying to Achieve

I'm trying to write a procedure in Excel, which is supposed to
- look at the pH values stored in column E, row by row
- compare this to a threshold value of 5
- enter a string ("pH range C") in column K, if the value in column E exceeds 5.

The Problem
I'm getting a type mismatch error, as shown in the source code below:

Sub PNEC()
 
Dim pH As Double
Dim PNEC As String
Dim usedrows As Double
Dim Row As Integer
 
usedrows = Worksheets("Fal").UsedRange.Rows.Count
pH = Worksheets("Fal").Columns("E").Value 'TYPE MISMATCH ERROR HERE
PNEC = Worksheets("Fal").Columns("K").Value
 
For Row = 2 To usedrows Step 1
 
If pH > 5 Then
PNEC = "pH range C"
End If
 
Next Row
 
End Sub
 

What I think Is Happening
In the sheet "Fal", all data in columns E and K are numeric except for the header row, which is text. I think it is this text that is causing the problem, conflicting with the "Double" variable type I've defined for pH.

My Questions

• Does my diagnosis seem reasonable, or have I misunderstood the problem entirely?

• How can I get around the problem?

Any insights gratefully received,

Lara

Hi

Your code has a number of fundermental errors (ie coulmns collection takes Integer arguments), but from your description I think something like this is what you want

    Dim pH As Double
    Dim PNEC As String
    Dim usedrows As Double
    Dim Row As Integer
 
    usedrows = Sheets("Fal").UsedRange.Rows.Count
    For Row = 2 To usedrows Step 1
        pH = Sheets("Fal").Cells(Row, 5)
        If pH > 5 Then
            Sheets("Fal").Cells(Row, 11) = "pH range C"
        Else
            '????
        End If
    Next Row

??

MTB

[Lara1]

OMG - it worked!!!!!!

MikeTheBike thankyouthankyouthankyou, I am SO grateful :-)

[Lara1]

Hi, I'm in trouble again.

This time, I'm trying to amend my procedure so that it will work through the spreadsheet line-by-line, checking the values in columns D and E, and selecting appropriate values to enter into columns M through to S.

At the moment, I can only get it to put a value in column M. If I try to get it to put values in columns M and N, I get a type mismatch error.

Here's the version that works:

Sub FalPNEC()
 
Dim pH As Double
Dim DOC As Double
Dim usedrows As Double
Dim Row As Integer
 
usedrows = Worksheets("Fal").UsedRange.Rows.Count
 
For Row = 2 To usedrows Step 1
 
pH = Sheets("Fal").Cells(Row, 5)
DOC = Sheets("Fal").Cells(Row, 4)
 
If pH < 7 And DOC <= 1 Then
Sheets("Fal").Cells(Row, 13) = "=pHBDOCAConA" 'this points to a named cell in another sheet, that contains a constant
 
Else: Sheets("Fal").Cells(Row, 13) = ""
 
End If
 
Next Row
 
End Sub
 

And here's where I run into trouble:

Sub FalPNEC()
 
Dim pH As Double
Dim DOC As Double
Dim usedrows As Double
Dim Row As Integer
 
usedrows = Worksheets("Fal").UsedRange.Rows.Count
 
For Row = 2 To usedrows Step 1
 
pH = Sheets("Fal").Cells(Row, 5)
DOC = Sheets("Fal").Cells(Row, 4)
 
If pH < 7 And DOC <= 1 Then
Sheets("Fal").Cells(Row, 13) = "=pHBDOCAConA" And _
Sheets("Fal").Cells(Row, 14) = "=pHBDOCAConB" 
 
Else: Sheets("Fal").Cells(Row, 13) = ""
 
End If
 
Next Row
 
End Sub
 

Can anyone advise on what's the problem here?

Many thanks,

Lara

[kadghar]

Hi, I'm in trouble again.

This time, I'm trying to amend my procedure so that it will work through the spreadsheet line-by-line, checking the values in columns D and E, and selecting appropriate values to enter into columns M through to S.


Can anyone advise on what's the problem here?

Many thanks,

Lara

May be if you remove the "and_" from the 16th line

HTH

[Lara1]

HTH, many thanks for the response (I only just saw it). You were right of course :-)