Consulting

Results 1 to 13 of 13

Thread: Solved: countif? within a loop

  1. #1
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location

    Solved: countif? within a loop

    Hi guys, Im turning to google and you guys as my boss is on leave.
    I'm using excel 2003.
    I have written a macro which basically get data from a large spreadsheet and inserts it into a standard template.

    basically there a some rows I don't want to go through the loop as the require manual massaging, i have marked these rows with an "X" in column 2 (B), all others have been left blank.

    See below for what I've got so far

    [VBA]Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 6/03/2013
    '
    'Set your variables
    Dim Filename_A As String
    Dim Filename_B As String
    Dim Filename_C As String
    Dim Filename_All As String

    Dim AccUse_97 As Single
    Dim AccUse_98 As Single
    Dim AccUse_99 As Single
    Dim AccUse_00 As Single
    Dim AccUse_01 As Single
    Dim AccUse_02 As Single
    Dim AccUse_03 As Single
    Dim AccUse_04 As Single
    Dim AccUse_05 As Single
    Dim AccUse_06 As Single
    Dim AccUse_07 As Single
    Dim AccUse_08 As Single
    Dim AccUse_09 As Single
    Dim AccUse_10 As Single

    Dim lCount As Integer
    Dim lNum As Integer
    Dim i As Integer

    'start
    Range("C7").Select
    i = 1

    'loop
    For lCount = 1 To 309
    Workbooks("NAME.xls").Activate
    Selection.Offset(1, 0).Select

    ' Assign data/values to variable
    Filename_A = Selection.Offset(0, 1).Value
    Filename_B = Selection.Offset(0, 2).Value
    Filename_C = Selection.Offset(0, 3).Value
    Filename_All = Filename_A & "_" & Filename_B & "_" & Filename_C

    ' Client information
    Section = Selection.Offset(0, 1).Value
    Licence = Selection.Offset(0, 2).Value
    Client = Selection.Offset(0, 3).Value
    Nom_Ent = Selection.Offset(0, 4).Value
    AccUse_97 = Selection.Offset(0, 6).Value
    AccUse_98 = Selection.Offset(0, 19).Value
    AccUse_99 = Selection.Offset(0, 32).Value
    AccUse_00 = Selection.Offset(0, 45).Value
    AccUse_01 = Selection.Offset(0, 58).Value
    AccUse_02 = Selection.Offset(0, 71).Value
    AccUse_03 = Selection.Offset(0, 84).Value
    AccUse_04 = Selection.Offset(0, 97).Value
    AccUse_05 = Selection.Offset(0, 110).Value
    AccUse_06 = Selection.Offset(0, 123).Value
    AccUse_07 = Selection.Offset(0, 136).Value
    AccUse_08 = Selection.Offset(0, 149).Value
    AccUse_09 = Selection.Offset(0, 162).Value
    AccUse_10 = Selection.Offset(0, 175).Value
    AccUse_11 = Selection.Offset(0, 188).Value

    Workbooks.Open Filename:= _
    "TEMPLATE.xls"
    Windows("HOU SP_template.xls").Activate
    Cells(1, 5).Value = Section
    Cells(2, 5).Value = Licence
    Cells(3, 5).Value = Client
    Cells(3, 14).Value = Nom_Ent
    Cells(12, 6).Value = AccUse_97
    Cells(15, 6).Value = AccUse_98
    Cells(18, 6).Value = AccUse_99
    Cells(21, 6).Value = AccUse_00
    Cells(24, 6).Value = AccUse_01
    Cells(27, 6).Value = AccUse_02
    Cells(30, 6).Value = AccUse_03
    Cells(33, 6).Value = AccUse_04
    Cells(36, 6).Value = AccUse_05
    Cells(39, 6).Value = AccUse_06
    Cells(42, 6).Value = AccUse_07
    Cells(45, 6).Value = AccUse_08
    Cells(48, 6).Value = AccUse_09
    Cells(51, 6).Value = AccUse_10
    Cells(54, 6).Value = AccUse_11

    Application.DisplayAlerts = False ' suppress overwrite warning message
    ActiveWorkbook.SaveAs Filename:= _
    "X\" & Filename_All, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close True

    i = i + 1

    Next lCount

    End Sub[/VBA]


    any help is greatly appreicated.
    thanks
    Last edited by Bob Phillips; 03-07-2013 at 03:02 AM. Reason: Added VBA tags

  2. #2
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    It seems to me that there is probably a fundamental flaw in your code.

    Icount does not appear to be used to iterate through anything with the result that all that would happen would be that you would populate the template and the save it, overwriting the previous file 309 times.

    Also, i does not appear to be doing anything.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Also, I cannot believe that you want to open the workbook and write it every time through the loop, before and after would make more sense would it not?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location
    thanks guys, I had some else write the loop portion, cause I'm only new to visual basic.

    it seems to work to get all the data out, but now that I want to exclude some data it doesn't - mainly cause I don't know what to put in.

    before & after probably would make more sense, but I have no idea what that means so I'll go search the forum/consult Google and get back to you

    thanks guys

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why don't you post example workbooks, tell us what needs to be done, and we can cut you some decent code.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location
    here is the template.
    the fluro green cells are those auto filled by the macro.

    I'll put some sample data up soon as I edit the post, as some of the data could be classed as sensitive I'll have to edit names etc.

    cheers
    Attached Files Attached Files

  7. #7
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location
    Here is the data file.

    the macro collects the information in the section, license, name & NE for the top portion of the template. then get the AU column from each year. saves each sheet using the section_license_name as the file name

    this process is then repeated for the entire sheets (there is more rows of data [finished on row 316] - but I think this is enough to get the idea)

    we made a decision to only collect the AU, as that way we can manually input the other data using the formulas in the template (in case there is more data to go in - there shouldn't be though).

    In Column B, I've single out the one I don't want within the loop as the license has changed hands in the time period and require manual manipulation.

    hope this is enough to go off.

    thanks in advance
    Attached Files Attached Files

  8. #8
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    The following is designed to be included in a module in the Template for VBA forum.
    [vba]Sub GetData()
    Dim wbsource As Workbook
    Dim wbtarget As Workbook
    Dim rngsource As Range
    Dim rngtarget As Range
    Dim i As Long, j As Long, k As Long
    Set wbtarget = ActiveWorkbook
    Set wbsource = Workbooks.Open("C:\Users\Doug\Downloads\data template_for VBA forum.xls")
    Set rngsource = wbsource.Sheets(1).Range("A1")
    Set rngtarget = wbtarget.Sheets(1).Range("A1")
    For i = 8 To wbsource.Worksheets(1).UsedRange.Rows.Count
    rngtarget.Offset(0, 4).Value = rngsource.Offset(i - 1, 3)
    rngtarget.Offset(1, 4).Value = rngsource.Offset(i - 1, 4)
    rngtarget.Offset(2, 4).Value = rngsource.Offset(i - 1, 5)
    rngtarget.Offset(2, 13).Value = rngsource.Offset(i - 1, 6)
    k = 8
    For j = 11 To 53 Step 3
    rngtarget.Offset(j, 5).Value = rngsource.Offset(i - 1, k).Value
    k = k + 13
    Next j
    wbtarget.SaveAs rngsource.Offset(i - 1, 3) & " - " & rngsource.Offset(i - 1, 4)
    Next i
    wbsource.Close
    End Sub
    [/vba]

    You will need to change the path to the data template_for VBA forum file

    It saves each file with a file name created by concatentating the Section and Licence Reference

  9. #9
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location
    got that working. cheers.

    any ideas for the loop minus the "X'

    thanks Doug

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    [vba]
    For i = 8 To wbsource.Worksheets(1).UsedRange.Rows.Count
    If rngsource.Offset(i - 1, 1) = "X" Then GoTo SkipThisRow
    .
    .
    .
    SkipThisRow:
    Next
    i [/vba]

  11. #11
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    Brend0n

    If you want the files saved with a concatenation of Section, Licence and Name, then use:

    [VBA]wbtarget.SaveAs rngsource.Offset(i - 1, 3) & " _ " & rngsource.Offset(i - 1, 4) & " _ " & rngsource.Offset(i - 1, 5)
    [/VBA]

    instead of:

    [VBA]wbtarget.SaveAs rngsource.Offset(i - 1, 3) & " - " & rngsource.Offset(i - 1, 4)
    [/VBA]

  12. #12
    VBAX Regular
    Joined
    Mar 2013
    Posts
    6
    Location
    thanks guys. all working well now

    kudos to all involved

    how do I change the tittle to solved?

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Thread tools

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •