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

macropod · #2 10-09-2012, 02:55 AM

Try the following macro. Depending on which sheet holds the data, you may need to change 'Sheet1' to the correct sheet name.

Code:

Sub Test()
Dim StrB As String, x As Long, LastRow As Long, i As Long
With ActiveWorkbook.Sheets("Sheet1")
  LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  For x = 1 To LastRow
    If .Cells(x, 1).Value <> "" Then
      StrB = .Cells(x, 2).Value
      .Cells(x, 3).Value = ((Len(StrB) - Len(Replace(StrB, "1w", ""))) * 1 + (Len(StrB) - Len(Replace(StrB, "2w", ""))) * 2 + _
        (Len(StrB) - Len(Replace(StrB, "3w", ""))) * 3 + (Len(StrB) - Len(Replace(StrB, "4w", ""))) * 4 + _
        (Len(StrB) - Len(Replace(StrB, "5w", ""))) * 5 + (Len(StrB) - Len(Replace(StrB, "6w", ""))) * 6 + _
        (Len(StrB) - Len(Replace(StrB, "7w", ""))) * 7 + (Len(StrB) - Len(Replace(StrB, "8w", ""))) * 8) / 2
    End If
  Next
End With
End Sub

tomlam · #3 10-09-2012, 03:56 AM

paul u really a expert in excel !!! Great, my problems solve ..thank a lot

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