Help with macro

[mp_robinson_uk]

Hi All,

This should be easy but I have no real ideas about VA. In xl I get a spreadsheet that has values from e2:ag452. Where e2 and ag452 can change. What I would like to have is a macro that just

a) adds a row with the number of numbers in each columb i.e.

e454 =COUNT(E2:E452)
f454 =COUNT(F2:F452)
....
ag454 =COUNT(AG2:AG452)
etc

b) adds a columb where I get the min value per row in a new column i.e.

ai2 =MIN(a2:ag2)
ai3 =MIN(a3:ag3)
....
ai452 =MIN(a452:ag452)
etc

Thanks very much,

Mick

[Jacob Hilderbrand]

Try this:

Option Explicit
 
Sub Macro1()
Dim LastRow As Long
Dim i As Long
LastRow = Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
For i = 5 To 6
Range(Cells(LastRow, i).Address).Value = _
"=Count(" & Cells(2, i).Address(False, False) & ":" & _
Cells(LastRow - 1, i).Address(False, False) & ")"
Next i
Range("E" & LastRow & ":F" & LastRow).AutoFill _
Destination:=Range("E" & LastRow & ":AG" & LastRow), Type:=xlFillDefault
For i = 2 To 3
Sheet1.Range("AI" & i).Value = "=Min(" & Range("E" & i & _
":AG" & i).Address(False, False) & ")"
Next i
Range("AI2:AI3").AutoFill _
Destination:=Range("AI2:AI" & LastRow), Type:=xlFillDefault
End Sub

[mp_robinson_uk]

Hi DRJ,

Thank you very very much! It works great!

Cheers,

Mick

[mp_robinson_uk]

Hi DRJ,

Sorry just noticed - it nearly works perfectly. The number of columns can change as well as the number of rows, I didn't explain it well enough.

I tried to modify you script but chaning

LastRow = Cells.Find(What:="*", LookIn:=xlValues, _ 
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

to

LastRow = Cells.Find(What:="*", LookIn:=xlValues, _ 
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 
LastCol = Cells.Find(What:="*", LookIn:=xlValues, _ 
SearchOrder:=xlByCols, SearchDirection:=xlPrevious).Col + 1

didn't work.

Help please.

Is there a good book to help making change like this?

Thanks,

Mick

[Jacob Hilderbrand]

Col and Cols cannot be used. You need to use Column or Columns.

LastCol = Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

We offer VBA Training at this site. It is a good introduction to using macros in general.

[mp_robinson_uk]

Hi DRJ,

Sorry but I can't even hack this I am totally lost. Perl I can hack - this i don't understand.

I tried

Sub Add_Col_Row()
Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
Rows("1:1").Select
    With Selection
        .Orientation = 90
    End With
    ActiveWindow.SplitRow = 1
    ActiveWindow.FreezePanes = True
    Cells.Select
    Selection.Columns.AutoFit
    LastRow = Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    LastCol = Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    For i = 5 To 6
        Range(Cells(LastRow, i).Address).Value = _
        "=Count(" & Cells(2, i).Address(False, False) & ":" & _
        Cells(LastRow - 1, i).Address(False, False) & ")"
    Next i
    Range("E" & LastRow & ":F" & LastRow).AutoFill _
    Destination:=Range("E" & LastRow & ":" & LastCol - 1 & LastRow), Type:=xlFillDefault
   For i = 2 To 3
        Sheet1.Range(LastCol - 1 & i).Value = "=Min(" & Range("E" & i & _
        ":AG" & i).Address(False, False) & ")"
    Next i
    Range("AI2:AI3").AutoFill _
    Destination:=Range("AI2:AI" & LastRow), Type:=xlFillDefault
End Sub

[Jacob Hilderbrand]

LastCol is a number but when using a range we need a letter for the column or we can use Cells(Row Num, Col Num).

Change This:

Destination:=Range("E" & LastRow & ":" & _
LastCol - 1 & LastRow), Type:=xlFillDefault

To This:

Destination:=Range("E" & LastRow & ":" & _
Cells(LastRow, LastCol - 1).Address(False, False)), Type:=xlFillDefault

If this doesn't work can you attach the workbook?

[mp_robinson_uk]

Hi DRJ,

Sorry still did not work for me. I have attached the apreadsheet I am using.

Can I use the paypal button at the top to give you a donation or is there a
better way of paying you.

Thanks,

Mick

[Jacob Hilderbrand]

Donations to the site are greatly appreciated.

[mp_robinson_uk]

Hi DRJ,

Just sent $25 dollars. Second payment in a week Dreamboat has helped me before. Is it possible to do the Excel VBA Training and Certification without the certification and if so is there a deduction. The training would be helpful to me but the certification would not.
I am not in IT or anything where I need it, but use xl often enough in my day to day job, that I would be interested in learning.

Regards,

Mick

[Jacob Hilderbrand]

Try this macro:

Option Explicit
 
Sub Add_Col_Row()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
Dim j As Long
Rows("1:1").Orientation = 90
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
Cells.Columns.AutoFit
LastRow = Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
LastCol = Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
For i = 5 To 6
Range(Cells(LastRow, i).Address).Value = _
"=Count(" & Cells(2, i).Address(False, False) & ":" & _
Cells(LastRow - 1, i).Address(False, False) & ")"
Next i
Range("E" & LastRow & ":F" & LastRow).AutoFill _
Destination:=Range("E" & LastRow & ":" & _
Cells(LastRow, LastCol - 1).Address(False, False)), Type:=xlFillDefault
For i = 2 To 3
Cells(i, LastCol).Value = "=Min(" & Range("E" & i & _
":" & Cells(i, LastCol - 1).Address).Address(False, False) & ")"
Next i
Range(Cells(2, LastCol).Address & ":" & Cells(3, LastCol).Address).AutoFill _
Destination:=Range(Cells(2, LastCol).Address & ":" & _
Cells(LastRow, LastCol).Address), Type:=xlFillDefault
End Sub

[Jacob Hilderbrand]

Hi DRJ,

Just sent $25 dollars. Second payment in a week Dreamboat has helped me before. Is it possible to do the Excel VBA Training and Certification without the certification and if so is there a deduction. The training would be helpful to me but the certification would not.
I am not in IT or anything where I need it, but use xl often enough in my day to day job, that I would be interested in learning.

Regards,

Mick

Thanks for the donation, we appreciate it. Right now the certification is basically free with the training. The normal price willl be $150 for both or $100 for the lessons and $50 for the certification. Right now since the certification is not complete it is free as a promotion. The lessons are all complete.

[Richie(UK)]

Hi guys,

Forgive me butting-in but couldn't we use FormulaR1C1 on the appropriate range to enter the formulas en masse rather than having to fill-down? Something like this:

Sub Add_Col_Row()
    Dim wsData As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
Set wsData = ThisWorkbook.Worksheets("all_modes_hold_extended")
With wsData
        .Rows("1:1").Orientation = 90
        .Cells.Columns.AutoFit
LastRow = .Cells.Find(What:="*", LookIn:=xlValues, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        LastCol = .Cells.Find(What:="*", LookIn:=xlValues, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
.Range(.Cells(LastRow, 1), .Cells(LastRow, LastCol - 1)).FormulaR1C1 = _
            "=COUNT(R[-" & LastRow - 2 & "]C:R[-1]C)"
        .Range(.Cells(1, LastCol), .Cells(LastRow, LastCol)).FormulaR1C1 = _
            "=MIN(RC[-" & LastCol - 2 & "]:RC[-1])"
    End With
End Sub

You may have to tinker with the desired starting points but the basic premise is there.

HTH

[Jacob Hilderbrand]

I don't like R1C1 style referencing so I don't usually use it. Just my preference.

[mp_robinson_uk]

Hi Jacob,

Thank you very very much!

I even managed to add another column with Count rather than min.

Thanks,

Mick

[Richie(UK)]

I don't like R1C1 style referencing so I don't usually use it. Just my preference.

It's certainly not as easy on the eye as the plain vanilla .Formula . However, it does have its uses and this type of scenario is one of them.

[mp_robinson_uk]

Hi Jacob,

Another question if I may. I have modified the macro to do a bit more.

Sub Add_Col_Row() 
Dim LastRow As Long 
Dim LastCol As Long 
Dim i As Long 
Dim j As Long 
Rows("1:1").Orientation = 90 
ActiveWindow.SplitRow = 1 
ActiveWindow.FreezePanes = True 
Cells.Columns.AutoFit 
LastRow = Cells.Find(What:="*", LookIn:=xlValues, _ 
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 
LastCol = Cells.Find(What:="*", LookIn:=xlValues, _ 
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1 
For i = 5 To 6 
Range(Cells(LastRow, i).Address).Value = _ 
"=Count(" & Cells(2, i).Address(False, False) & ":" & _ 
Cells(LastRow - 1, i).Address(False, False) & ")" 
Next i 
Range("E" & LastRow & ":F" & LastRow).AutoFill _ 
Destination:=Range("E" & LastRow & ":" & _ 
Cells(LastRow, LastCol - 1).Address(False, False)), Type:=xlFillDefault 
For i = 2 To 3 
Cells(i, LastCol).Value = "=Min(" & Range("E" & i & _ 
":" & Cells(i, LastCol - 1).Address).Address(False, False) & ")" 
Next i 
Range(Cells(2, LastCol).Address & ":" & Cells(3, LastCol).Address).AutoFill _ 
Destination:=Range(Cells(2, LastCol).Address & ":" & _ 
Cells(LastRow, LastCol).Address), Type:=xlFillDefault 
For i = 2 To 3 
Cells(i, LastCol + 1).Value = "=Count(" & Range("E" & i & _ 
":" & Cells(i, LastCol - 1).Address).Address(False, False) & ")" 
Next i 
Range(Cells(2, LastCol + 1).Address & ":" & Cells(3, LastCol + 1).Address).AutoFill _ 
Destination:=Range(Cells(2, LastCol + 1 ).Address & ":" & _ 
Cells(LastRow, LastCol + 1).Address), Type:=xlFillDefault 
Cells(LastRow, LastCol + 1).ClearContents
Cells(LastRow, LastCol).ClearContents
Cells(1,LastCol + 1).Value = "number of corners with violations"
Cells(1,LastCol).Value = "largest violations"
Cells(LastRow, 2).Value = "number of violations per corner"
Columns(LastCol).Select
Selection.Sort Key1:=Range(LastCol), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Insert Shift:=xlToRight 
End Sub

The sort doesn't appear to work - well it sorts but then gives me an error

Runtime error 1004

Method Rang of object _Global failed

and does not do the next line of inserting the column.

Any ideas?

Thanks,

Mick

[Zack Barresse]

It's certainly not as easy on the eye as the plain vanilla .Formula . However, it does have its uses and this type of scenario is one of them.

I couldn't agree more. I don't like R1C1, but it definitely has it's uses. You can code around it (which I've done plenty of) but sometimes you just can't beat the functionality.

[Jacob Hilderbrand]

Hi Jacob,

Thank you very very much!

I even managed to add another column with Count rather than min.

Thanks,

Mick

You're Welcome.

Take Care

[Jacob Hilderbrand]

It's certainly not as easy on the eye as the plain vanilla .Formula . However, it does have its uses and this type of scenario is one of them.

That is true for sure. I have used it a few times. For one project I was adding a bunch of formulas to an ever changing range. I used Offset to get to the cells I wanted and R1C1 formulas to get the correct formulas for each section.