Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
I have a worksheet set up, along with a macro. It changes the value of
column G, but only if column T does not contain the letter "N". If column T DOES contain the letter "N", then column G is unchanged The code below only works if the letter N is uppercase. I want to change to code to look for both uppercase and lower case "N". If .Cells(X, "G").Value 0 And .Cells(X, "G").Value < 0.1458333 And ..Cells(X, "T") < "N" Then .Cells(X, "G").Value = "3:30" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
richzip wrote:
I have a worksheet set up, along with a macro. It changes the value of column G, but only if column T does not contain the letter "N". If column T DOES contain the letter "N", then column G is unchanged The code below only works if the letter N is uppercase. I want to change to code to look for both uppercase and lower case "N". Add UCASE(): If .Cells(X, "G").Value 0 And .Cells(X, "G").Value < 0.1458333 And UCASE(.Cells(X, "T")) < "N" Then .Cells(X, "G").Value = "3:30" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Hi,
change this ...Cells(X, "T") < "N".... to ...UCase(.Cells(X, "T")) < "N" ... this will convert .Cells(X, "T") 's value to upper case before conducting the test < "N". So it will always be comparing an upper case value to the (upper case) "N". The complimentary function is LCase() which converts a string to its lower case version. Hope this helps. Regards Bernie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
If .Cells(X, "G").Value 0 And .Cells(X, "G").Value < 0.1458333 _
And UCase(.Cells(X, "T")) < "N" Then .Cells(X, "G").Value = "3:30" "richzip" wrote: I have a worksheet set up, along with a macro. It changes the value of column G, but only if column T does not contain the letter "N". If column T DOES contain the letter "N", then column G is unchanged The code below only works if the letter N is uppercase. I want to change to code to look for both uppercase and lower case "N". If .Cells(X, "G").Value 0 And .Cells(X, "G").Value < 0.1458333 And .Cells(X, "T") < "N" Then .Cells(X, "G").Value = "3:30" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Or you could use a worksheet function like this...
=IF(AND(G3 0, G3 < 0.1458333,UPPER(T3<"N")),"3:30","whatever ") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Another option is to tell excel that you want every text comparison in this
module to be non-case sensitive. Add this at the top of your code: Option Compare Text ps. I would use: If .Cells(X, "G").Value 0 _ And .Cells(X, "G").Value < 0.1458333 _ And .Cells(X, "T") < "N" Then with .Cells(X, "G") .numberformat = "hh:mm:ss" .Value = timeserial(3,30,0) end with end if I think it removes any ambiguity (03:30:00 or 00:03:30 for the next person who reads your code). richzip wrote: I have a worksheet set up, along with a macro. It changes the value of column G, but only if column T does not contain the letter "N". If column T DOES contain the letter "N", then column G is unchanged The code below only works if the letter N is uppercase. I want to change to code to look for both uppercase and lower case "N". If .Cells(X, "G").Value 0 And .Cells(X, "G").Value < 0.1458333 And .Cells(X, "T") < "N" Then .Cells(X, "G").Value = "3:30" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |