Macro Help
Closed
EM2010
Posts
2
Registration date
Friday November 30, 2012
Status
Member
Last seen
December 3, 2012
-
Nov 30, 2012 at 12:48 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 3, 2012 at 05:00 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 3, 2012 at 05:00 PM
Related:
- Macro Help
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Run macro when cell value changes - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 1, 2012 at 07:26 AM
Dec 1, 2012 at 07:26 AM
Try this
Public Sub Fillin()
Dim lastRowUsed As Long
Sheets("Detailed YTD 2012").Select
lastRowUsed = getItemLocation("*", Cells)
Range("Z2").FormulaR1C1 = "=+RC[-4]-RC[-6]+1"
Range("Z2").NumberFormat = "General"
Range("AB2").FormulaR1C1 = "=RC[-1]-RC[-8]+1"
Range("AB2").NumberFormat = "General"
Range("AC2").FormulaR1C1 = "=IF(RC[-8]<2012,RC[-7]-366,RC[-9])"
Range("AD2").FormulaR1C1 = "=IF(RC[-9]<2012,RC[-8],RC[-10]+366)"
Range("AE2").FormulaR1C1 = "=RC[-4]-RC+1"
Range("AE2").FormulaR1C1 = "=RC[-4]-RC[-2]+1"
Range("AE2").NumberFormat = "General"
Range("AF2").FormulaR1C1 = "=RC[-2]-RC[-3]"
Range("AF2").NumberFormat = "General"
Range("AG2").FormulaR1C1 = "=IF(RC[-7]>728,RC[-2]/RC[-1],RC[-5]/RC[-7])"
Range("AH2").FormulaR1C1 = "=IF(RC[-1]<100%,RC[-1],100%)"
Range("AI2").FormulaR1C1 = "=RC[-1]*RC[-27]"
Range("AJ2").FormulaR1C1 = "=RC[-28]-RC[-1]"
Range("Z2:AJ2").Select
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z" & lastRowUsed)
Range("Z2:Z" & lastRowUsed).Select
Range("AB2:AJ2").Select
Selection.AutoFill Destination:=Range("AB2:AJ" & lastRowUsed), Type:=xlFillDefault
End Sub
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
EM2010
Posts
2
Registration date
Friday November 30, 2012
Status
Member
Last seen
December 3, 2012
Dec 3, 2012 at 02:55 PM
Dec 3, 2012 at 02:55 PM
I used the above macro but now it exceeds the last row and fills up the blank rows starting with col z .
for eg if the last row used in row 300 the macro now exceeds line 300 and fills up upto line 500.
for eg if the last row used in row 300 the macro now exceeds line 300 and fills up upto line 500.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Dec 3, 2012 at 05:00 PM
Dec 3, 2012 at 05:00 PM
Are you looking for last used row or last used column Z cell ?
The code is to find out what is the last used cell in the sheet. If you want to see what is the last used cell in Z is then
change
lastRowUsed = getItemLocation("*", Cells)
to
lastRowUsed = getItemLocation("*", COLUMNS(26))
The code is to find out what is the last used cell in the sheet. If you want to see what is the last used cell in Z is then
change
lastRowUsed = getItemLocation("*", Cells)
to
lastRowUsed = getItemLocation("*", COLUMNS(26))
