VBA code for count w and write into another column in excel

tomlam · #1 10-08-2012, 05:44 AM

Hi all

I face a problems that is i need to count how many w in column B and write into column C frequently in Excel .how to write VBA code to automatic
count and write total w as follow column c. Because i need to estimate the performance of thousand of students frequently

Column A / column B / column C
peter / good;(2w2w)slow/ 4w
mary/ good /
john/ not bad(1w1w1w1w)/ 4w
alxe /good/
tom /bad(2w4w5w3w) / 14w
simon/ good(1w2w3w)/ 6w

if we put the formula in column c
=((LEN(B2)-LEN(SUBSTITUTE(B2,"1w","")))*1+(LEN(B2)-LEN(SUBSTITUTE(B2,"2w","")))*2+(LEN(B2)-LEN(SUBSTITUTE(B2,"3w","")))*3+(LEN(B2)-LEN(SUBSTITUTE(B2,"4w","")))*4+(LEN(B2)-LEN(SUBSTITUTE(B2,"5w","")))*5+(LEN(B2)-LEN(SUBSTITUTE(B2,"6w","")))*6+(LEN(B2)-LEN(SUBSTITUTE(B2,"7w","")))*7+(LEN(B2)-LEN(SUBSTITUTE(B2,"8w","")))*8)/2

it will work but if want to use vba progamme, how to write

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To lastrow
If (Cells(1, ("a"))) <> "" Then Value.Cells(x, ("c")) =
=((LEN(B2)-LEN(SUBSTITUTE(B2,"1w","")))*1+(LEN(B2)-LEN(SUBSTITUTE(B2,"2w","")))*2+(LEN(B2)-LEN(SUBSTITUTE(B2,"3w","")))*3+(LEN(B2)-LEN(SUBSTITUTE(B2,"4w","")))*4+(LEN(B2)-LEN(SUBSTITUTE(B2,"5w","")))*5+(LEN(B2)-LEN(SUBSTITUTE(B2,"6w","")))*6+(LEN(B2)-LEN(SUBSTITUTE(B2,"7w","")))*7+(LEN(B2)-LEN(SUBSTITUTE(B2,"8w","")))*8)/2

it dont work, the vba code should write as ?

Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to write the degree, minute second format in excel	Jamal NUMAN	Excel	12	07-09-2018 11:44 PM
count w and write into another column in excel	tomlam	Excel	5	10-07-2012 06:09 AM
Is it possible to write down a number starting with zero in excel cell?	Rahmat_uk20	Excel	3	06-08-2012 02:26 AM
Read text Report file with VBA and write parsed fields to Excel workbook	tpcervelo	Excel Programming	1	01-05-2012 10:14 PM
Count in Excel	zanat0s	Excel	3	06-09-2011 10:53 AM