PDA

View Full Version : Solved: Help with macro



mp_robinson_uk
11-12-2004, 02:50 AM
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
11-12-2004, 03:15 AM
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
11-12-2004, 03:28 AM
Hi DRJ,

Thank you very very much! It works great!

Cheers,

Mick

mp_robinson_uk
11-12-2004, 03:46 AM
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
11-12-2004, 03:52 AM
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
11-12-2004, 04:31 AM
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
11-12-2004, 05:41 AM
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
11-12-2004, 06:00 AM
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
11-12-2004, 06:22 AM
Donations to the site are greatly appreciated. :)

mp_robinson_uk
11-12-2004, 06:33 AM
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 (http://www.vbaexpress.com/training.htm) 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
11-12-2004, 06:39 AM
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
11-12-2004, 06:44 AM
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 (http://www.vbaexpress.com/training.htm) 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)
11-12-2004, 06:47 AM
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 SubYou may have to tinker with the desired starting points but the basic premise is there.

HTH

Jacob Hilderbrand
11-12-2004, 07:19 AM
I don't like R1C1 style referencing so I don't usually use it. Just my preference.

mp_robinson_uk
11-12-2004, 07:26 AM
Hi Jacob,

Thank you very very much!

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

Thanks,

Mick

Richie(UK)
11-12-2004, 07:37 AM
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
11-12-2004, 08:59 AM
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
11-12-2004, 09:54 AM
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
11-12-2004, 04:04 PM
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
11-12-2004, 04:08 PM
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.

Jacob Hilderbrand
11-12-2004, 04:12 PM
Hi Jacob,

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

Columns(LastCol).Select
Selection.Sort Key1:=Range(LastCol), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Insert Shift:=xlToRight


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

The Key1 part needs to be a single cell:
Key1:=Range(LastCol)
So we need to use Cells to assign one cell from the column:
Key1:=Range(Cells(1,LastCol).Address)
Also since these are formulas the column won't sort since the data for the formulas has not changed. You can sort all the data if you wanted instead of just the one column and leave the Key1 the same to sort by that column.

mp_robinson_uk
11-15-2004, 03:23 AM
Hi Jacob,

Got it to sort correctly by changing to the cell as you suggested.

Thank you very much for all your help!

Thanks,

Mick

mp_robinson_uk
11-15-2004, 03:34 AM
Hi All,

I thought I would post the complete code just in case it was ever off use to someone else. Thanks for all your help.

Thanks,

Mick :hi:



Option Explicit

Sub Add_Col_Row()

Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
Dim j As Long
Dim Prompt As String
Dim Title As String
Dim Value As Double
Dim c As Range
Dim Counter As Integer

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"
Prompt = "Input the number that you want to add to each value."
Title = "Number Input"
Value = Val(InputBox(Prompt, Title))
For Each c In Range(Cells(2, "E").Address & ":" & Cells(LastRow - 1, LastCol - 1).Address)
If IsNumeric(c.Value) Then
c.Value = c.Value + Value
End If
If c.Value >= 0 And IsNumeric(c.Value) Then
c.ClearContents
End If
Next
Counter = LastRow - 1
Do While Counter > 1
if Cells(Counter, LastCol).Value = 0 AND IsNumeric(Cells(Counter, LastCol)) Then
Cells(Counter, LastCol).EntireRow.Delete
End If
Counter = Counter - 1
Loop
Columns(LastCol).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
Selection.Sort Key1:=Range(Cells(2,LastCol).Address), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns(LastCol).Select
Selection.Insert Shift:=xlToRight
End Sub

Jacob Hilderbrand
11-15-2004, 03:59 AM
Hi Jacob,

Got it to sort correctly by changing to the cell as you suggested.

Thank you very much for all your help!

Thanks,

Mick

You're Welcome

Take Care