Share via

Check to see that each variable is a positive decimal

Anonymous
2022-10-06T14:19:16+00:00

I have assigned variables as Integers and need to check that they are all positive decimal numbers. I know the command is IsNumber, but is there a way to check multiple variables without have a bunch of if statements? All the variables: coherencelength As Integer, tuningrange As Integer, power As Integer, sweeprate As Integer, kclockcount As Integer, kclockdepth As Integer need to be confirmed as positive decimals.

I know I could do this for each, but that would make the code very long and inefficient. Any help would be greatly appreciated. 

 If IsNumber(coherencelength.value) = True Then 

    Continue 

 Else 

     MsgBox coherencelength & " is not a positive decimal"

This is my code:

Sub columnlocation() ' identifies which column each parameter is in for Sheet1

Dim coherencelengcol As Integer, tunrangecol As Integer, averagepowercol As Integer, sweeprtecol As Integer, kclockctcol As Integer, kclockdepthcol As Integer

Dim coherencelength As Integer, tuningrange As Integer, power As Integer, sweeprate As Integer, kclockcount As Integer, kclockdepth As Integer

coherencelengcol = Application.Match("Coherence Length (mm)", Worksheets("Sheet1").Range("A1:Z1"), 0) ' Application.Match looks up the position of the value, 0 means its looking for an exact match case-sensitive

coherencelength = Worksheets("Sheet1").Cells(sysrow, coherencelengcol) ' assigning the data to a variable that will be used in updateWD

tunrangecol = Application.Match("Tuning Range (nm)", Worksheets("Sheet1").Range("A1:Z1"), 0)

tuningrange = Worksheets("Sheet1").Cells(sysrow, tunrangecol)

averagepowercol = Application.Match("Power (mW)", Worksheets("Sheet1").Range("A1:Z1"), 0)

power = Worksheets("Sheet1").Cells(sysrow, averagepowercol)

sweeprtecol = Application.Match("Sweep Rate (kHz)", Worksheets("Sheet1").Range("A1:Z1"), 0)

sweeprate = Worksheets("Sheet1").Cells(sysrow, sweeprtecol)

kclockctcol = Application.Match("K-Clock Count", Worksheets("Sheet1").Range("A1:Z1"), 0)

kclockcount = Worksheets("Sheet1").Cells(sysrow, kclockctcol)

kclockdepthcol = Application.Match("K-Clock set for Imaging Depth in air (mm)", Worksheets("Sheet1").Range("A1:Z1"), 0)

kclockdepth = Worksheets("Sheet1").Cells(sysrow, kclockdepthcol)

End Sub

Microsoft 365 and Office | Excel | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-10-06T21:18:14+00:00

    for range.Find, would you know how to proceed in converting MATCH to FIND

    Yes. But I wonder if Bernie did just that in your other thread.

    Word to wise: It is "bad practice" to post related questions in multiple threads. It results in redundant work for responders and possible confusion for you.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-10-06T20:19:30+00:00

    Yes, for debugging there always was a MATCH case allowing it to succeed. for range.Find, would you know how to proceed in converting MATCH to FIND

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-10-06T18:33:17+00:00

    Is there a way to use Range.Find without needing the lines of IsError to find the column indexes of each header?

    Well, you might use On Error. But I suspect that IsError is better in that context.

    OTOH, you can disregard my "is risky" comment if you know the Match will succeed.

    As for range.Find vs. Application.Match: I suspect that range.Find is a better choice. But read the help page carefully in order to use range.Find correctly.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-10-06T18:20:59+00:00

    Is there a way to use Range.Find without needing the lines of IsError to find the column indexes of each header?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2022-10-06T15:33:51+00:00

    If you have Dim coherencelength As Integer, then coherencelength.value makes no sense, and IsNumber(coherencelength) is tautological.

    The syntax coherencelength.value presumes that coherencelength is type Range. It is not.

    And since coherencelength is type Integer (btw, type Long is more reliable in general), of course its value "is a number".

    If you want to test for positive decimal numbers, the test would be coherencelength > 0 or >= 0, depending on what you mean by "positive" (non-negative?).

    The statements:

    coherencelengcol = Application.Match("Coherence Length (mm)", Worksheets("Sheet1").Range("A1:Z1"), 0)

    coherencelength = Worksheets("Sheet1").Cells(sysrow, coherencelengcol)

    are risky without testing IsError(coherencelengcol).

    The point is: Application.Match returns an error if the match fails.

    Finally, Application.Match looks up the position of the value, 0 means its looking for an exact match *case-sensitive* is incorrect.

    The Match function is case-insensitive, not case-sensitive.

    When the last parameter is zero, Match looks for an "exact" match -- but again, "exact" here means not case-sensitive.

    Was this answer helpful?

    0 comments