Countif? within a loop

[Brend0n]

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


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


any help is greatly appreciated.
thanks

[Doug Robbins]

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.

[Bob Phillips]

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?

[Brend0n]

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

[Bob Phillips]

Why don't you post example workbooks, tell us what needs to be done, and we can cut you some decent code.

[Brend0n]

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

[Brend0n]

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

[Doug Robbins]

The following is designed to be included in a module in the Template for VBA forum.

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


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 concatenating the Section and Licence Reference

[Brend0n]

got that working. cheers.

any ideas for the loop minus the "X'

thanks Doug

[SamT]

For i = 8 To wbsource.Worksheets(1).UsedRange.Rows.Count
If rngsource.Offset(i - 1, 1) = "X" Then GoTo SkipThisRow
.
.
.
SkipThisRow:
Next i

[Doug Robbins]

Brend0n

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


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


instead of:


wbtarget.SaveAs rngsource.Offset(i - 1, 3) & " - " & rngsource.Offset(i - 1, 4)

[Brend0n]

thanks guys. all working well now

kudos to all involved

how do I change the tittle to solved?

[SamT]

Thread tools