Excel vba if first char of cell is 1 thenClosed

Question

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

Quentin · Accepted Answer

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. :)

kevin · Answer

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

spintua · Answer

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

Excel vba if first char of cell is 1 then

3 responses

Similar discussions

Subscribe To Our Newsletter!