Posts by hgus393

Re: Transferring values from each sheet to a new sheet

Hum I think I am on the right way here, I am checking the number of rows that has values ie COUNTA for each worksheet and by that being able to determine how many rows to transferred (copied) to the new sheet. The problem I am facing now is getting the values into the new worksheet with all the values.. anybody know hos to do this?

Re: Need to Append a value in cell after checking if a value exists

What is it you are trying to do? Reading the code and looking at the worksheet it is not all together clear what you are attepting to do. Please explain a bit more.

Re: Format Sheet Objects

Does this help any?

Sub DoesThisHelp()
Dim MyShape


Set MyShape = Worksheets(1)
MyShape.Shapes(1).Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientBrass


End Sub

//Robert

Re: Transferring values from each sheet to a new sheet

Right sorry,

Please see the attached example I would like to have it in VBA!

//Robert

Hello all,

What I am trying to do is transferring values from a column in each sheet to a new sheet in the workbook, does anybody know how to do this?

//Robert

Re: Sum based on row count

Never mind sussed it out.. :thanx: alot gmccreedy !!!

//Robert

Re: Sum based on row count

Quote from gmccreedy

something like this?

Code

Sub SpecialSum()
     
    Dim rct As Long
    rct = Application.WorksheetFunction.CountA(Range("B:B"))
    ActiveCell.FormulaR1C1 = "=SUM(R" & [rct] & "C:R" & [1] & "C)"
     
End Sub

note the " " in the formula.

HTH

Display More

Nearly..The first cell has a non numeric value and as such this macro returns
a ##### value, tired replacing the [1] by [-1] but it does not work....

Re: Need to Append a value in cell after checking if a value exists

Could you post the workbook?
//Robert

Hello All,

Is is possible to create a sum function on the active cell that checks the number of rows as its base?
I have tried the following but it does not work...anybody knows if it is possible to use this method?

Sub SpecialSum()


Dim rct As Long
rct = Application.WorksheetFunction.CountA(Range("B:B"))
ActiveCell.FormulaR1C1 = "=SUM(R &[Rct]C:R[-1]C)"


End Sub

:thanx:

Robert

Re: For each construct excluding the last two sheets

Quote from Dave Hawley

Hi Robert

Try

Code

Sub Duh()
     
    Dim lLoop As Long
     
    For lLoop = 1 To Worksheets.Count - 2
        MsgBox Worksheets(lLoop).Name
    Next lLoop
End Sub

Hi Dave,
This works really good thank you, the problem is that I am trying to run a countif formula in all the sheets except for the last two sheets.
The code looks like this:

Sub Duh()


Dim wks as worksheet
Dim rowc as long


For Each wks In ActiveWorkbook.Worksheets


rowc = Application.WorksheetFunction.CountA(wks.Range("B:B"))


For i = 2 To rowc
wks.cells(i, 16) = "=COUNTIF(C[-14],RC[-14])"
Next i
Next wks
End sub

Do you have any ideas to eliminate the last two sheets from this macro?

//Robert :confused:

Hi all,

I am really stupid I cannot get a macro to exclude the last two sheets in a workbook when I am using a for each construct. I tired with the following and that did not fly:

Sub Duh()


Dim wks As Worksheet


For Each wks In ActiveWorkbook.Worksheets(Worksheets.Count - 2)
...
...
Next wks
End sub

Anyone know how to exclude the two last sheets from the for each construct?

/Robert

Re: For each construct does not work

Quote from norie

The reason this doesn't work is because you only define r once and only for the active worksheet.

Try defining it within the loop and by referencing the worksheet.

Code

Sub duplicates() 
    Dim cell As Range 
    Const Colour = 5 
    Dim wks As Worksheet 
    Dim r As Range 
          
    Application.ScreenUpdating = False 
     
    For Each wks In ActiveWorkbook.Worksheets 
        Set r = wks.Range("p2:p500")     
        For Each cell In r 
            Select Case cell.Value 
            Case Is < 2 
                cell.Interior.ColorIndex = xlNone 
            Case Is = "" 
                cell.Interior.ColorIndex = xlNone 
            Case Is >= 2 
                cell.Interior.ColorIndex = Colour 
            End Select 
        Next cell 
    Next wks 
    Application.ScreenUpdating = True 
End Sub

Display More

Thank you Norie ...learnt a new thing today... :eureka: :thanx:

Hello all,

I am using a countif formula on a range in all sheets in a workbook (specifically range P2 to P500). If the occurence is >= 2 then I want to colour the background in the cell blue, however I want to do this accross all worksheets in the active workbook. For some reason it does not work, can anyone see what I have done wrong?

Sub duplicates()
Dim cell As Range
Const Colour = 5
Dim wks As Worksheet
Dim r As Range
Set r = Range("p2:p500")




Application.ScreenUpdating = False


For Each wks In ActiveWorkbook.Worksheets
For Each cell In r
Select Case cell.Value
Case Is < 2
cell.Interior.ColorIndex = xlNone
Case Is = ""
cell.Interior.ColorIndex = xlNone
Case Is >= 2
cell.Interior.ColorIndex = Colour
End Select
     Next cell
          Next wks
Application.ScreenUpdating = True
End Sub

:thanx:
Robert

Re: Row Delete by Countif function

Quote from norie

[vba]
Sub RowDelete()

Dim Lrow As Long
Dim i As Long
Dim Data As Range
Dim rng As Range
Set Data = Range("D2:x38")

Lrow = Data.Rows.Count

Lrow = Lrow + Data.Row - 1

Application.ScreenUpdating = False
For i = Lrow To 1 Step -1
Set rng = Range("D" & i & ":X" & i)
If Application.WorksheetFunction.CountIf(rng, "<>0") = 0 Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub[/vba]

Display More

Sorry was a bit to quick to answer...This did the stuff.. :thanx:
:yourock:

Robert

Re: Row Delete by Countif function

Quote from norie

Robert

I take it you want to remove the rows where the data under all the dates is 0?

If so why are you using CountIf on the whole row?

It will never equal zero because of the values in columns A-C.

Display More

Yes I want to remove the rows where the data under all the dates is 0.

Hum see what you mean about columns A to C - Do have any clue to get round that?

Robert

Re: Row Delete by Countif function

Quote from norie

Robert

What exactly are you trying to do?

Could you not just sort blank rows to the bottom?

Hello Norie,

I think it is better that I send the information I have in the worksheet (removing about 4500 rows but still the basics of what it looks like), hopefully thing will get clearer then:

Robert

Re: Row Delete by Countif function

Quote from youtocc

What about this?

Code

Sub DeleteEmptyRows()
    Dim LastRow As Long, i As Long
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    For i = LastRow To 1 Step -1
        If WorksheetFunction.Sum(Rows(i)) = 0 Then
            Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

Yes that would work fine if I did not have Row headings and if I did not have figures with - signs and plus sign (I could get a sum = 0 for those occasions even though the row has data that I need)

My data looks sort of like this...

Fig1 Fig2
Dogs 0 0
Cats 1 2
Birds 2 1

Then the countif formula cannot be applied.

Robert

Re: Row Delete by Countif function

Quote from Bob Phillips

My suggestion works fine for me.

I must be a complete idiot I cannot get it to work. :duh:

Re: Row Delete by Countif function

Ok,

I have enclosed a workbook with the basic jist of what I am trying to do, but in VBA. Ie if the countif value renders a zero then remove the row. Hope you understand what I mean.
Robert

Re: Row Delete by Countif function

Quote from Bob Phillips

Try

If Application.COUNTA(Rows(i)) - Application.COUNTIF(Rows(i),0) = 0 Then

Nope nothing happens..