Search : in
By :

Excel vba if first char of cell is 1 then

Last answer on Oct 22, 2009 2:51:40 am BST Bill, on Aug 18, 2008 6:04:06 pm BST 
 Report this message to moderators

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

Configuration: Windows XP
Firefox 2.0.0.11

Best answers for « Excel vba if first char of cell is 1 then » in :
Excel VBA Msg Box when cell meets criteria Show
Repainting a cell using excel VBA Show
[VBA] Detecting changes in cell Show [VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Excel - Using IF statement to add 1 to total ShowExcel - Using IF statement to add 1 to total Issue Solution Note Issue I am trying to get a formula to add + 1 to a total cell based on the word typed. Example : If I type the word Duty in cells B3:B18 then add + 1 to cell B35. If...
Repainting a cell using excel VBA ShowRepainting a cell using excel VBA Issue Solution Note Issue I am writing a code in excel VBA to generate a report.I am totally new to VBA.Here's my code Private Sub CommandButton1_Click() Dim a1 As Integer Dim d1 As Integer Dim...
How to Create Msg/Popup to notifications in Excel VBA? ShowHow to Create Msg/Popup to notifications in Excel VBA? Issue Solution Note Issue I need a Excel VB script to notify/popup a message when the number of the letter U on a calendar exceeds 6, 8, 12 etc. It will have to check the range of...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Ask your question Reply

1

kevin, on Sep 25, 2008 11:02:44 pm BST
  • +1

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

   
Reply to kevin

2

Quentin, on Oct 31, 2008 7:42:29 am GMT
  • +5

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

   
Reply to Quentin

4

 Will, on Oct 22, 2009 2:51:40 am BST

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

   
Reply to Will
Ask your question Reply
They need your help