Excel vba if first char of cell is 1 then

[Closed]
Report
-
 Not a member yet. -
Hello,

I have a question about "if then" in excel vba

I've been looking all over the web for an answer or example and now matter what code I try I cannot get the right syntax.

I have a column named "code" which contains item codes and I want to test the value and place a particular value in another cell based on the comparison.

Allow me to illustrate.

Code
1AALIBOLTS10000


now, I want to be able to look at the first char of cell A1 and if it is a "1" then place "its a 1" into column b cell 1

then I need to check the second char of cell A1 and if it is a "C" then place "its a C" into column e cell 1

and so on and so on until I have compared all the values I wish to compare.

I hope this makes sense. I've been pulling my hair out trying to get this accomplished.

Any help would be greatly appreciated!

Thanks,

Bill

3 replies

the code for your question will look something like this.

Dim mycheck as boolean

endrange = range("A65000").end(xlUp).row

for I = 1 to endrange

mycheck = range("A" & i).value Like "1*"

if mycheck = true then

range("B" & i).value="Its a One"
end if

my2check = range("A" & i).value Like "?c*"

if my2check = true then

range("E" & i).value="Its a C"
end if

next I



Hope it helps. :)
19
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thanks so much, this is handy for the piles of things with zeros I must process.
Bill,

Look into the vb functions Mid and Len, this will allow you to select the right char.

Example:

val = "1AALIBOLTS10000"

tempval = mid(val,1,1) 'Mid will extract from val starting at pos 1 for a lenght of 1 the return value will be 1
'Select your cell
'Activecell text set = "its a " & tempva

Kevin, Thank you very much. I'm 10 weeks into VBA , writing a commodities trading program that depends heavely on charts.
I started studing the markets using a 64k VisaCalc spread sheet on a Tandy computer, pre-PC, then to Lotus Symphony, only because 123 would not do OHLC charts then to 123 for many years .The transition to Excel a few years ago was not to bad but other than keyboard macors never used VBA; WHAT A DIFFERENCE! i'm thanking you and others for sharing your knowledge of VBA. .

I use Excel functiion extensively in spreadsheeets and delighted to find some of my most used available in VBA. WalkenBackk lists them in his book "Excell 2007 Power Programming with VBA but dosen't explain their use well. So thanks to you and others who share great code on the internet.

Because I'm so new to VBA I use these same funcction in Excel to alter repeatious VBA code in
Excell where something changes often, replicate it and copy it to the VBA editor. For me turns a weeks worth of stumbling around in to an hour When I learn VBA I'm sure that won't be necessary.

Lastley this code is fantastic, powerful, simple and works perfect, I was surprised to find soo much code on the internet that is very poor, just plain wrong or too complicated at least for me right now.

Amazingly my trading program which places 94 charts instantantly on 2 26-inch monitors and updates them tick by tick with streaming date published out of tradestation and picked up with excel's TRD function, is about complete and works fantastic . So thanks again to you and others for sharing. .

Doug H
try this

Assuming ur text is in the range A1 on sheet 2

dim w as worksheet
dim r as range

set w = thisworkbook.sheets(2)

for each r in w.usedrange.cells
if left(r.value) =1 then
"enter you arguments"
End if
Next