VBA - Run macro in only one column

Question

I have the code working exactly the way I'd like to, however I don't want it to skip onto another column. I just want my macro to run inside column C then exit. I am new to VBA in excel, so please pardon my faults. Any help would be much appreciated. Thanks in advance.

    Sub CopyValuetoRange()
'
' CopyValuetoRange Macro

Dim search_range As Range, Block As Range, last_cell As Range
  Dim first_address$
  Set search_range = ActiveSheet.UsedRange
  Set Block = search_range.Find(what:="*", _
    after:=search_range.SpecialCells(xlCellTypeLastCell), _
    LookIn:=xlValues, searchorder:=xlColumns, searchdirection:=xlDown)
  If Block Is Nothing Then Exit Sub

  Set Block = Block.CurrentRegion
  first_address$ = Block.Address
  Do
    Block.Select
    Selection.End(xlDown).Select
    ActiveCell.CurrentRegion.Rows(2).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "=R[-1]C"

    'MsgBox "Next Block Range"
    Set last_cell = Block.Cells(Block.Rows.Count)
    Set Block = search_range.FindNext(after:=last_cell).CurrentRegion
  Loop Until Block.Address = first_address$ 'ActiveSheet.Range("C26").End(xlDown).Row


End Sub

Here is something I modified from something I found that will essentially do the same thing, however it puts the first cells value into all cells in the range. And this macro actually stays in Column C, since I found recently because it's not a region, it's a range.

Is there a way to change the following to add a formula to all cells in the range that points to the first cell in the range?

Sub Macro5()

    Dim Rng As Range
    Dim RngEnd As Range
    Dim rngArea As Range

        Set Rng = Range("C1")
        Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlDown)
        If RngEnd.Row < Rng.Row Then Exit Sub

        Set Rng = Range(Rng, RngEnd)

        On Error GoTo ExitSub
        Set Rng = Rng.SpecialCells(xlCellTypeConstants)

        For Each rngArea In Rng.Areas
            rngArea.Value = rngArea.Cells(Rng.Rows.Count, 1).Value
        Next rngArea


ExitSub:
    ' Macro will exit here if the range is empty.

End Sub

It would be easier if you describe what you want the macro to do (not what it's not supposed to do). — Dirk N
– Dirk N, Commented Sep 17, 2012 at 15:05
My appologies for not being clearer. Start at "C1", look downward for an active cell region, once found select all but the top cell in the current region(within comlumn C only), apply FormulaR1C1 = "=R[-1]C" to each of those cells(essentially making the top cell editable and all the cells below in that region will reflect the same), after changing those cells formula, go to the next active cell region within column C in the downward direction and perform the same thing all over again for that region and so on... — cheapkid1
– cheapkid1, Commented Sep 17, 2012 at 18:08

Daniel · Accepted Answer · 2012-09-17 15:31:24Z

1

How about you change your search_range, so that you only search Column C?

  Set search_range = ActiveSheet.Range("C:C")
  Set Block = search_range.Find(what:="*", _
    LookIn:=xlValues, searchorder:=xlColumns, searchdirection:=xlDown)

answered Sep 17, 2012 at 15:31

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cheapkid1 Over a year ago

Thanks Daniel, however that will only work if there's nothing on either side of column C. What if you have something like this?: !Valid XHTML.

cheapkid1 Over a year ago

What if I have cells filled on either side of column C? I just want the macro to focus on column C only. Please see my valid html link above for example. Any help would be appreciated.

Daniel Over a year ago

@cheapkid1 I saw your image, I just don't understand. Are you saying that you want the code to not change anything outside of column C? If so, you should rethink using CurrentRegion.

cheapkid1 Over a year ago

Yes that's what I'm saying. Thanks so much, that makes sense. I'll try a different approach. Any suggestions what I should use instead??

Daniel Over a year ago

I'm not sure what you're trying to do... if all you are trying to do is update Column C's formulas to be equivalent to "=R[-1]C" then running this: Range("C2", Range("C2").End(xlDown).Address).FormulaR1C1 = "=R[-1]C" will do that for everything except C1. That's how I understand what you're explaning, but it seems too simple to be what you want.

|

cheapkid1 · Accepted Answer · 2012-09-26 18:13:10Z

Here's what I have, it's not pretty but it works. I added a column on both sides then removed them after the macro went through the entire column:

Sub CopyFirstCellInRangeInOneColumn()
'
' CopyValuetoRange Macro
Dim search_range As Range, Block As Range, last_cell As Range
  Dim first_address$
  ''
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  ''
  Set search_range = ActiveSheet.Range("D:D")
  Set Block = search_range.Find(what:="*", _
    LookIn:=xlValues, searchorder:=xlColumns, searchdirection:=xlDown)

  'Set search_range = ActiveSheet.UsedRange
  'Set Block = search_range.Find(What:="*", _
  '  After:=search_range.SpecialCells(xlCellTypeLastCell), _
  '  LookIn:=xlValues, SearchOrder:=xlColumns, SearchDirection:=xlDown)


  If Block Is Nothing Then Exit Sub

  Set Block = Block.CurrentRegion
  first_address$ = Block.Address
  Do
    Block.Select
    Selection.End(xlDown).Select
    ActiveCell.CurrentRegion.Rows(2).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "=R[-1]C"

    MsgBox "Next Block Range"
    Set last_cell = Block.Cells(Block.Rows.Count)
    Set Block = search_range.FindNext(After:=last_cell).CurrentRegion
  Loop Until Block.Address = first_address$ 'ActiveSheet.Range("C26").End(xlDown).Row

    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft


End Sub

Collectives™ on Stack Overflow

VBA - Run macro in only one column

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related