Flux rss
Search : in
By : Relevance Date Username
Statut : Not resolved

Excel vba if first char of cell is 1 then

Bill, on Monday 18 August 2008 à 18:04:06
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
Reply to Bill  Report this message to moderators Go to last message

1


  • This message seems useful, vote!
  • Report this message to moderators
kevin, on Thursday 25 September 2008 à 23:02:44
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


  • This message seems useful, vote!
  • Report this message to moderators
 Quentin, on Friday 31 October 2008 à 07:42:29
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
Ask your question Reply

Résultats pour Excel vba if first char of cell is 1 then

Firmnual Help If cell is blank go to next ce Hello, EXCEL FORMULA If cell is not blank automatically look go to next cell (across) and if the cell has text in it the show the text ? I have 35 colums some have info (dates) and some do not I am trying to show the last intry in the colum at the... en.kioskea.net/forum/affich-26805-firmnual-help-if-cell-is-blank-go-to-next-ce
Excel Macro Help Please (Solved) Hello Everyone, I am sking for some help with an Excel macro please. I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with... en.kioskea.net/forum/affich-11146-excel-macro-help-please
Copy data into next empty cell in new sheet Hello, I am trying to make a 2 sheet workbook which has the input details in Sheet1 A1 and this is changed weekly but the info in this cell should then be inserted into the first blank cell in Column A in Sheet 2. I have tried this code (below) but it... en.kioskea.net/forum/affich-16567-copy-data-into-next-empty-cell-in-new-sheet

Résultats pour Excel vba if first char of cell is 1 then

Excel tips : How to insert date In an Excel cellExcel tips : How to insert date In an Excel cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press “CTRL”+ “;” in the chosen cell. To insert current... en.kioskea.net/faq/sujet-313-excel-tips-how-to-insert-date-in-an-excel-cell
To insert an image in the FAQTo insert an image in the FAQ Method Where to find images? Inserting screenshots can help to understand an explanation. Here is how to improve tips on Kioskea (FAQ). For this trick, we opted to use the site Imageshack.us. Note:... en.kioskea.net/faq/sujet-67-to-insert-an-image-in-the-faq
How to insert picture background on Microsoft!How to insert picture background on Microsoft! To make a work sheet more appealing, you can add picture to your background. Below is a tip for doing so. Open your Microsoft and select “Format”> “Backgrounds” > “Printed... en.kioskea.net/faq/sujet-334-how-to-insert-picture-background-on-microsoft
1 2 3 Next

Résultats pour Excel vba if first char of cell is 1 then

SecurityHello, I am urgently looking for some help of Excel VBA... I need to protect excel workbooks... The users should be aloud to edit information in unlocked cells, but they may NOT copy and paste sheets (or anything more than 1 cell at a time)... The... en.kioskea.net/forum/affich-32328-security
Bulk pasting problem with mac excelHello, I have a formula which i want to paste into 10,000 cells at once. because they are cells 1 to 10,000 in one column so it is relatively easy to select all 10,000 of them. however, when I press the paste button, it only pastes into the first... en.kioskea.net/forum/affich-6057-bulk-pasting-problem-with-mac-excel
Lost data after saving Powerpoint presentatioHello, We use VBA to generate Powerpoint presentation using data from Excel. Data inserted into charts datasheet disappears after we save it. We stepped through the code to determine that after the file is saved, we cannot access the data from the... en.kioskea.net/forum/affich-28802-lost-data-after-saving-powerpoint-presentatio
1 2 3 Next

Résultats pour Excel vba if first char of cell is 1 then

Cryptography - Public-key systems The principle of asymmetric encryption (also called public-key encryption) first appeared in 1976, with the publication of a work about cryptography by Whitfield Diffie and Martin Hellman. In an asymmetric cryptosystem (or public-key cryptosystem),... en.kioskea.net/crypto/clepublique.php3
Style sheets - Introduction The concept of style sheets first appeared in 1996 when the W3C published a new recommendation entitled "Cascading Style Sheets", or CSS for short. The principle behind style sheets involves using a single document to store the page layout... en.kioskea.net/css/cssintro.php3
Mobile telephony - Introduction The first generation of mobile telephony (written 1G) operated using analogue communications and portable devices that were relatively large. It used primarily the following standards: AMPS (Advanced Mobile Phone System), which appeared in 1976 in... en.kioskea.net/telephonie-mobile/reseaux-mobiles.php3
1 2 3 Next