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
5 9956
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
OMG - it worked!!!!!!
MikeTheBike thankyouthankyo uthankyou, I am SO grateful :-)
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 1,295
Recognized Expert Top Contributor
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
HTH, many thanks for the response (I only just saw it). You were right of course :-)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: LJgrnl |
last post by:
I've got a type mismatch error that's driving me nutty. Variable blnNoData
has the initial value False. If a recordset comes back empty (both .EOF and
..BOF are true) then blnNoData is set to True. I then have an if statement to
determine what to write to the screen depending on blnNoData. As long as the
if statement is true (doesn't have to go to the else clause), the code runs
fine. Otherwise, a type mismatch error is returned. In...
|
by: Mike |
last post by:
I am getting a type mismatch error when I do a bulk insert.
---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---
The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
|
by: Ange T |
last post by:
Hi there,
I'm having pain with the VB behind an Access form. The form is used to
create reports in Excel based on the details entered in the form. This
has always worked without error on my machine (NT4, Access 2k),
however as soon as I attempt to create anything on another machine
(NT4, Access 2k) which most users will be working from, I receive an
automation error.
The problem line with the code is:
|
by: Edward S |
last post by:
I budget for a Project in an Excel sheet as illustrated below. The
months below are usually a 2 year period i.e. 24 months, though it
could be over 24 months depending upon a Project. I then need to
input this in an Access database, where I do a comparison with the
Actual cost. The table “TblBudget” in Access is made of 4
fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
At the moment this method is very cumbersome....
|
by: deko |
last post by:
So I've decided to convert from Early Binding to Late Binding. Now that
I've been baptized, I need some instruction in the faith.
My former ways were thus:
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
and then I went off in debauchery like this:
| |
by: jason.teen |
last post by:
Hi,
I am having trouble importing a spreadsheet from Excel into an Access
Database.
I have noticed that even specifying the Cell Type of the Excel Data
Cell, When I import it, still get Import Errors. Like Type Mismatch.
The funny thing is that its all done automatically via the Wizard, so I
dont even specify what the field type should be in my database, but it
|
by: wasishincar |
last post by:
Hi all,
I wrote a small app, which could read text file and fill the data from
text file to a newly created excel file. It works fine on my computer,
but did not succeed on someone's. I caught an exception which told it
occurred when "trying to read or write protected memory. It usually
means the memory was damaged". What did I need to check? The account is
administrator, and it should be fine to write file at any place in that
PC.
|
by: cht13er |
last post by:
Good day all, I'm a newish VBA coder who was taught from Google Groups
- so thanks to you all very much!
I have run into a minor problem, I hope you can give me some help ...
I'm running office 2003 for this one.
In excel I have a program running ... and based on the results of an
optionbutton (called "Form2.Radio_AllParameters") I want to change the
SQL that runs a query in access. If the button is True, the SQL should
read "WHERE...
|
by: House817 |
last post by:
I consistently get an overflow error at the "SOxBenefits =" (source code below), while coding in a macros from within Excel, Microsoft XP. I've tried the following:
1. setting a variable equal to a number * CLong(#)
2. changing the variable type, as well as making sure all subsequent variables are of the same type. When attempting to use the ULong type, Excel gives me a mismatch error. I found a thread on how to link a windows file, but the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |