+ Reply to Thread
Results 1 to 2 of 2

Insert a new row below text and formatting Macro issue

Hybrid View

  1. #1
    Registered User
    Join Date
    09-08-2017
    Location
    London, ENgland
    MS-Off Ver
    2010
    Posts
    1

    Insert a new row below text and formatting Macro issue

    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
    Attached Files Attached Files
    Last edited by 6StringJazzer; 09-08-2017 at 10:10 AM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Insert a new row below text and formatting Macro issue

    
    Sub LABOUR()
    Main ("Labour")
    End Sub
    
    Sub PLANT()
    Main ("Plant")
    End Sub
    
    Sub Materials()
    Main ("Materials")
    End Sub
    Sub Other()
    Main ("Other")
    End Sub
    Sub OHP()
    Main ("OH&P")
    End Sub
    Sub Main(T As String)
    
     R = Cells.Find(What:=T, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Row
            
        Rows(R + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows(R).Copy
        Rows(R + 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    
        With Range("A" & R + 1 & ":E" & R + 1).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
    
        Cells(R, 7).Value = "HRS"
        Cells(R, 8).FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-7]C[-7]:R[-3]C[-6],2,FALSE)"
        Cells(R, 9).FormulaR1C1 = "=RC[-3]*RC[-1]"
    End Sub
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formatting issue / Wrap Text
    By SnowBrian in forum Excel General
    Replies: 5
    Last Post: 04-23-2014, 05:28 PM
  2. [SOLVED] insert Pre-defined text and hide shapes issue
    By crimzon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 06:06 AM
  3. Formatting Issue While Exporting to Text File
    By Fanylion in forum Excel General
    Replies: 5
    Last Post: 03-29-2014, 01:40 PM
  4. [SOLVED] Excel 2013 issue with text formatting
    By Sthlm in forum Excel General
    Replies: 7
    Last Post: 10-23-2013, 01:52 AM
  5. Formatting Number as text Issue.
    By DunkUK101 in forum Excel General
    Replies: 10
    Last Post: 02-12-2013, 08:43 AM
  6. [SOLVED] Insert shift down issue macro issue
    By CC64 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2012, 10:57 PM
  7. Formatting Issue: Wrap Text and Shrink to Fit
    By K m in forum Excel General
    Replies: 5
    Last Post: 06-13-2012, 09:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1