Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make cells relative (Excel XP)

    I've written the code below and what I need to do is to cycle through the B column and find each instance of a phrase "Spots / Demo" and do a routine each time it finds those words instead of jumping back to cell B1 each time.
    Dim IntCtr As Integer
    IntCtr = 0
    If IntCtr = 0 Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Columns.AutoFit
    IntCtr = IntCtr + 1
    Range("B1").Select
    End If
    If ActiveCell = "SPOTS / DEMO " Then
    Range("B1:B3").Select
    Selection.EntireRow.Insert
    Range("A4:A6").Select
    Selection.Cut Destination:=Range("C1:C3")
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "TIMEFRAME"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "PROGRAM"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "INVENTORY_NUMBER"
    End If

    Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make cells relative (Excel XP)

    I don't understand the first part, but the loop could look like this:

    Dim lngRow As Long
    For lngRow = ActiveSheet.Range("B65536").End(xlUp).Row To 1 Step -1
    If ActiveSheet.Range("B" & lngRow).Value = "SPOTS / DEMO" Then
    ActiveSheet.Range(lngRow & ":" & (lngRow + 2)).Insert
    ActiveSheet.Range("B" & lngRow).Value = "TIMEFRAME"
    ActiveSheet.Range("B" & (lngRow + 1)).Value = "PROGRAM"
    ActiveSheet.Range("B" & (lngRow + 2)).Value = "INVENTORY_NUMBER"
    ActiveSheet.Range("A" & (lngRow + 3) & ":A" & (lngRow + 5)).Cut ActiveSheet.Range("C" & lngRow & ":C" & (lngRow + 2))
    End If
    Next lngRow

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Make cells relative (Excel XP)

    Was the code in <post#=377116>post 377116</post#> of no use to you then?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make cells relative (Excel XP)

    John,

    All apologies!!! For some reason, our email system marked the responses to my email as spam and I have just now realized it. I was thinking I had breached some sort of etiquette, since I did not get any responses as I normally do.

    I will have a look at your solution and carry on.

    Thank you for getting back to me.

    The first part of the code makes room for the first occurance (sort of like intpage in your word example).

    Thanks again, Hans.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make cells relative (Excel XP)

    John,

    I know I responded to your other email, but I just want you guys to know how much I appreciate this resource.

Posting Permissions

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