Hi All
I'm having difficulty with a particular macro and wondered if anyone can help.
The workbook has 2 tabs
1) NEW
2) RATES
The 'NEW' tab has 5 blue buttons along the top (Labour, Plant, Materials, Other, OHP) that should each have the following function -
1) Find the word associated with the button
2) Insert a row BELOW this word
-Problem 1 - This function doesn't seem to work properly, it finds the word but then when i insert the row below, the code just picks up on the actual row number.
-Problem 2 - When i insert the row the format is different as Col A to E should be merged.
3) In this new cell below the correct word I insert a list from the 'RATES' tab
4) A VLOOKUP then adds the appropriate rate for this item and a few other bits.
The main issue i'm having is that when recording my macro the code picks up on the specific row i choose (i.e. Row 11, Row 12) instead of the one under the right word for the button pressed. Therefore if i have already inserted rows on the sheet, the new line comes under the wrong category.
It might be easier to look through it and i'll try to answer any question.
I've only added the Labour and Plant macro at present (CODE below). If you press the Plant button once and the Labour button once the rows are added into the right place. Then if you press Plant button again the row is in the wrong place.
Really appreciate any help.
David
(A newby here and to Macros)
Sub LABOUR()
'
' LABOUR Macro
'
'
Cells.Find(What:="LABOUR", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("10:10").Select
Selection.Copy
Rows("9:9").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A9:E9").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=RATES!$A$2:$A$6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("G9").Select
ActiveCell.FormulaR1C1 = "HRS"
Range("H9").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-7]C[-7]:R[-3]C[-6],2,FALSE)"
Range("I9").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("I10").Select
End Sub
Sub PLANT()
'
' PLANT Macro
'
'
Cells.Find(What:="PLANT", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows("11:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("12:12").Select
Selection.Copy
Rows("11:11").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11:E11").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=RATES!$D$2:$D$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("G11").Select
ActiveCell.FormulaR1C1 = "HRS"
Range("H11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-9]C[-4]:R[-1]C[-3],2,FALSE)"
Range("I11").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("I12").Select
End Sub
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
Bookmarks