Posts by norie

    Try this.

    Re: Using Range with Dim and For yields Application-defined or object-defined error

    Cells also needs a worksheet reference, try this.

    1. With Sheets("one")
    2. .Range(.Cells(31 * i - 26, 2), .Cells(31 * i - 3, 4)).Copy Sheets("two").Range("A2")
    3. End With

    Also, it should just be Cells not Range(Cells)

    1. Sheets("three").Range("B4").Copy Sheets("four").Cells(1 * i + 6, 3)
    2. Sheets("three").Range("B5").Copy Sheets("four").Cells(1 * i + 6, 4)
    3. Sheets("three").Range("B6").Copy Sheets("four").Cells(1 * i + 6, 5)

    Re: looping column worksheetfunction vlookup

    Instead of using multiple Vlookups function where the column changes you could use one Match function to find the row where cl is in the table.

    Then you could use Cells to return the values you want, or you might even be able to use resize and transpose.

    Re: go to cell of match result


    It should just be match_range not Range(match_range).

    There is another problem though, what exactly are you trying to do here?

    1. next_cell = Application.goto match_range.Cells(Application.Match(x.Value, match_range, 0))

    You appear to want to goto a cell in match_range and assign a value from the cell you goto to the variable next_cell.

    That's not possible with the above code.

    Also later on in the code you have this.

    1. Cells(next_cell).Select
    2. ActiveCell.Value = temp_result
    3. next_cell = next_cell.Offset(0, 1)

    What are you trying to do here?

    Re: Applying a border to the same cells on every worksheet with VBA

    Eh, I know it doesn't work.:)

    Do you mean that wherever 'P12' is found in a cell you want to format that cell and the five cells below it?

    If you do then this might work.

    1. With rng.Resize(6).Borders(xlEdgeLeft)
    2. .LineStyle = xlContinuous
    3. .ColorIndex = 0
    4. .TintAndShade = 0
    5. .Weight = xlMedium
    6. End With

    By the way it still isn't clear where you want to loop to.

    Re: Applying a border to the same cells on every worksheet with VBA

    Based solely on the posted code, which isn't really clear, try this.

    The 2 main things that aren't clear are why a static range like 'C2:C7' to apply the formatting and this,

    1. Loop Until ActiveCell = .Range("C28")

    are you trying to loop until the active cell value equals the value in C28?