Macro IF > 0, adds 1 to cell

[Craig Farish]

Hi all
I am fairly new to writing macros and am completely baffled by what you guys know.
I have been reading for hours on various websites etc for something that i could manipulate into what i need but whatever i attempt to do so it fails.
Example:
I have data in a column A,the number of rows in column A varies from from workbook to workbook. I want to loop through the data in this column and whenever the data is greater than 0 i want to add 1 to a specific cell (C4)
The logic seems very simple but i just have no idear how to write this in code.

Any suggestions will be greatly appreciated.

 

[SteveO59L]

What makes you believe you need code to do this?

 

[Craig Farish]

I need a macro to run this code in many columns and list the number of times the data is greater than 0 in varying cells.

The example has been simplified to make it clear.

 

[Michael M]

Hi and welcome to the Board

Try

sub adder()
Dim r As Long, lr As Long, c As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
c = 0
    For r = lr To 1 Step -1
        If Range("A" & r).Value <> 0 Then
            Range("C4").Value = c + 1
            c = c + 1
        End If
    Next r
    End Sub

 

[SteveO59L]

So, why cant you use countif ?

 

[Michael M]

Hi Steve.
I agree about the COUNTIF....but I I'm imagining that Craig has probably got thousands of lines that vary in number, so the formula would have to change constantly.
Using a snippet might just save him the search down the column, so to speak.

 

[Craig Farish]

Thanks for your replies!

I have lots of data to sort and hundreds of workbooks to get through, hence the need for the macro.

My intension is to open all the workbooks and run the macro to input the data into specific cells which can then be read by a master workbook via a vlookup. Never really done anything like this before so its kind of experimental. Fingers crossed.

Thanks again guys!

 

[Michael M]

Craig
Is this being done to lots of worksheets in each workbook ??

 

[Craig Farish]

Sorry

I have lots of .csv files where i need the macro to run and store the data it reads into a specific cell. e.g

Cell G2 will hold the qty of cells will 0 in column A
Cell G3 will hold the qty of cells will 0 in column B
Cell G4 will hold the qty of cells will 0 in column C
Cell G5 will hold the qty of cells will 0 in column D
Cell G6 will hold the qty of cells will 0 in column E

I will have a master .xslx that will look at each .csv file and read the specific cells (G2,G3,G4,G5,G6). This will enable me to report on all the .csv files and how much missing data i have across them all.

Well thats the idea

 

[Craig Farish]

Ok i used the CountA & Count IF within a macro and it works perfectly.

Range("O3").Select
ActiveCell.FormulaR1C1 = "Entries"
Range("O4").Select
ActiveCell.FormulaR1C1 = "Mapprod"
Range("O5").Select
ActiveCell.FormulaR1C1 = "Weight"
Range("O6").Select
ActiveCell.FormulaR1C1 = "Cost"
Range("O7").Select
ActiveCell.FormulaR1C1 = "Erate"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=COUNTA(C[-3])-1"
Range("P4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-10],""#N/A"")"
Range("P5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-7],0)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-5],0)"
Range("P7").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],0)"

On to the next part

Thanks for your replies.