Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-08-2012, 05:44 AM
tomlam tomlam is offline VBA code for count w and write into another column in excel Windows 7 64bit VBA code for count w and write into another column in excel Office 2007
Advanced Beginner
VBA code for count w and write into another column in excel
 
Join Date: Oct 2012
Posts: 33
tomlam is on a distinguished road
Default VBA code for count w and write into another column in excel

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
Reply With Quote
  #2  
Old 10-09-2012, 02:55 AM
macropod's Avatar
macropod macropod is offline VBA code for count w and write into another column in excel Windows 7 64bit VBA code for count w and write into another column in excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,225
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-09-2012, 03:56 AM
tomlam tomlam is offline VBA code for count w and write into another column in excel Windows 7 64bit VBA code for count w and write into another column in excel Office 2007
Advanced Beginner
VBA code for count w and write into another column in excel
 
Join Date: Oct 2012
Posts: 33
tomlam is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code for count w and write into another column in excel How to write the degree, minute second format in excel Jamal NUMAN Excel 12 07-09-2018 11:44 PM
VBA code for count w and write into another column in excel 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
VBA code for count w and write into another column in excel Read text Report file with VBA and write parsed fields to Excel workbook tpcervelo Excel Programming 1 01-05-2012 10:14 PM
VBA code for count w and write into another column in excel Count in Excel zanat0s Excel 3 06-09-2011 10:53 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft