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

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