By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,099 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Novice Excel VBA coder - type mismatch error

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. Sub PNEC()
  2.  
  3. Dim pH As Double
  4. Dim PNEC As String
  5. Dim usedrows As Double
  6. Dim Row As Integer
  7.  
  8. usedrows = Worksheets("Fal").UsedRange.Rows.Count
  9. pH = Worksheets("Fal").Columns("E").Value 'TYPE MISMATCH ERROR HERE
  10. PNEC = Worksheets("Fal").Columns("K").Value
  11.  
  12. For Row = 2 To usedrows Step 1
  13.  
  14. If pH > 5 Then
  15. PNEC = "pH range C"
  16. End If
  17.  
  18. Next Row
  19.  
  20. End Sub
  21.  
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
Jan 11 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 634
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:

Expand|Select|Wrap|Line Numbers
  1. Sub PNEC()
  2.  
  3. Dim pH As Double
  4. Dim PNEC As String
  5. Dim usedrows As Double
  6. Dim Row As Integer
  7.  
  8. usedrows = Worksheets("Fal").UsedRange.Rows.Count
  9. pH = Worksheets("Fal").Columns("E").Value 'TYPE MISMATCH ERROR HERE
  10. PNEC = Worksheets("Fal").Columns("K").Value
  11.  
  12. For Row = 2 To usedrows Step 1
  13.  
  14. If pH > 5 Then
  15. PNEC = "pH range C"
  16. End If
  17.  
  18. Next Row
  19.  
  20. End Sub
  21.  
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
Expand|Select|Wrap|Line Numbers
  1.     Dim pH As Double
  2.     Dim PNEC As String
  3.     Dim usedrows As Double
  4.     Dim Row As Integer
  5.  
  6.     usedrows = Sheets("Fal").UsedRange.Rows.Count
  7.     For Row = 2 To usedrows Step 1
  8.         pH = Sheets("Fal").Cells(Row, 5)
  9.         If pH > 5 Then
  10.             Sheets("Fal").Cells(Row, 11) = "pH range C"
  11.         Else
  12.             '????
  13.         End If
  14.     Next Row
??

MTB
Jan 11 '08 #2

P: 11
OMG - it worked!!!!!!

MikeTheBike thankyouthankyouthankyou, I am SO grateful :-)
Jan 11 '08 #3

P: 11
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:
Expand|Select|Wrap|Line Numbers
  1. Sub FalPNEC()
  2.  
  3. Dim pH As Double
  4. Dim DOC As Double
  5. Dim usedrows As Double
  6. Dim Row As Integer
  7.  
  8. usedrows = Worksheets("Fal").UsedRange.Rows.Count
  9.  
  10. For Row = 2 To usedrows Step 1
  11.  
  12. pH = Sheets("Fal").Cells(Row, 5)
  13. DOC = Sheets("Fal").Cells(Row, 4)
  14.  
  15. If pH < 7 And DOC <= 1 Then
  16. Sheets("Fal").Cells(Row, 13) = "=pHBDOCAConA" 'this points to a named cell in another sheet, that contains a constant
  17.  
  18. Else: Sheets("Fal").Cells(Row, 13) = ""
  19.  
  20. End If
  21.  
  22. Next Row
  23.  
  24. End Sub
  25.  
And here's where I run into trouble:
Expand|Select|Wrap|Line Numbers
  1. Sub FalPNEC()
  2.  
  3. Dim pH As Double
  4. Dim DOC As Double
  5. Dim usedrows As Double
  6. Dim Row As Integer
  7.  
  8. usedrows = Worksheets("Fal").UsedRange.Rows.Count
  9.  
  10. For Row = 2 To usedrows Step 1
  11.  
  12. pH = Sheets("Fal").Cells(Row, 5)
  13. DOC = Sheets("Fal").Cells(Row, 4)
  14.  
  15. If pH < 7 And DOC <= 1 Then
  16. Sheets("Fal").Cells(Row, 13) = "=pHBDOCAConA" And _
  17. Sheets("Fal").Cells(Row, 14) = "=pHBDOCAConB" 
  18.  
  19. Else: Sheets("Fal").Cells(Row, 13) = ""
  20.  
  21. End If
  22.  
  23. Next Row
  24.  
  25. End Sub
  26.  
Can anyone advise on what's the problem here?

Many thanks,

Lara
Jan 14 '08 #4

kadghar
Expert 100+
P: 1,295
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
Jan 14 '08 #5

P: 11
HTH, many thanks for the response (I only just saw it). You were right of course :-)
Jan 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.