Basic VBA Help with Ifs, fors, and relative referencing

[jdautel]

Hey guys, so I'm trying to write a macro that uses relative referencing based on the active cell selected. Currently I have what is written below. I'm trying to make it so it checks each cell all the way to the left in the corresponding A cell to see if it is bold. If it is bold then I want it to output an x in the range b:c. For some reason whenever I run this macro it is freezing up my excel which means (I think), it is trying to calculate WAAAYYY more than I'm meaning for it to. Any ideas?

Option Explicit

Sub xmarksthespot()
Dim a As Range, b As Object, c As Object, cells As Object, cells2 As Object
ActiveCell.Select
Set b = ActiveCell
Set c = ActiveCell.Offset(83, 0)

For Each cells In Range("b:c")
    For Each cells2 In Range("A2:A83")
        If cells2.Font.Bold = True Then
        cells = "x"
        End If
    Next
Next



End Sub

[SamT]

Range("b:c") = 2 columns x 1,048,576 = 2 Million Cells
For each of those cells, check the same 83 cells in column A


Set c = ActiveCell.Offset(83, 0) := Set c = to the cell 83 rows blow the active cell

I'm trying to make it so it checks each cell all the way to the left in the corresponding A cell to see if it is bold. If it is bold then I want it to output an x in the range b:c

Range("b:c") is two columns.

I deleted everything that is not used in your code, so you can see what is happening

Sub xmarksthespot() 
    Dim cells As Object, cells2 As Object 
     
    For Each cells In Range("B1:C1048576") 'Full address of ("b:c")
        For Each cells2 In Range("A2:A83") 
            If cells2.Font.Bold = True Then 
                cells = "x" 
            End If 
        Next 
    Next      
     
End Sub

[jdautel]

I was wondering if that is what was happening. How do I set it to scan the cells I actually need it to scan then?

[jdautel]

How can I create a range that is based upon the active cell selected

[Paul_Hossler]

Hey guys, so I'm trying to write a macro that uses relative referencing based on the active cell selected. Currently I have what is written below. I'm trying to make it so it checks each cell all the way to the left in the corresponding A cell to see if it is bold. If it is bold then I want it to output an x in the range b:c. For some reason whenever I run this macro it is freezing up my excel which means (I think), it is trying to calculate WAAAYYY more than I'm meaning for it to. Any ideas?

I'm trying to make it so it checks each cell all the way to the left in the corresponding A cell to see if it is bold

Q1: Do you mean the cell in column A of the ActiveCell's row?



If it is bold then I want it to output an x in the range b:c.

Q2: Range B:C is a little over 2 million cells. Do you want an "X" in all 2M, or do you mean an "X" in column B and column of the row that the active cell is in?



For some reason whenever I run this macro it is freezing up my excel which means (I think), it is trying to calculate WAAAYYY more than I'm meaning for it to.

Q3: I'll be it is. You're checking 2 million x 82 cells = 164,000,000 cells. That's a lot


Any ideas?

Q4: Yes. Tell us what you want to do, and not how you think you need to do it



Q5:

a. No need to select the ActiveCell
b. No need to set it to b unless you want to return to it
c. c is 83 ROWS after the ActiveCell, same COLUMN. Where did the 83 come from?
d. I think your loops are wrong
e. You said you wanted to use relative addressing of ActiveCell, but your macro does not use ActiveCell (sets it but never uses it)
f. You loop A2:A83 for some reason
f. You set c but never use it

Option Explicit

Sub xmarksthespot()
    Dim a As Range, b As Object, c As Object, cells As Object, cells2 As Object

    ActiveCell.Select
    Set b = ActiveCell
    Set c = ActiveCell.Offset(83, 0)
     
    For Each cells In Range("b:c")
        For Each cells2 In Range("A2:A83")
            If cells2.Font.Bold = True Then
                cells = "x"
            End If
        Next
    Next
End Sub

I think there might be a terminology disconnect, so I'm guessing that you were looking for something like these, which might get you started.
Else I/we will need more information

Option Explicit
 
Sub xmarksthespot_guess()
    With ActiveCell.EntireRow
        If .cells(1).Font.Bold Then
            .cells(2).Value = "X"
            .cells(3).Value = "X"
        End If
    End With
End Sub
    

Sub xmarksthespot_guess_2()
    Dim C As Range
    
    For Each C In Range("A2:A83").cells
        With C
            If .Font.Bold Then
                .Offset(0, 1).Value = "X"
                .Offset(0, 2).Value = "X"
            End If
        End With
    Next
End Sub


Sub xmarksthespot_guess_3()
    Dim C As Range
    
    For Each C In Selection.cells
        With C.EntireRow
            If .cells(1).Font.Bold Then
                .cells(2).Value = "X"
                .cells(3).Value = "X"
            End If
        End With
    Next
End Sub

[jdautel]

Hmm.ok. So I selected the active cell for b to create a 83 cell range from that cell downward. So if i select cell y1 it would select a range from y1:y83. Then, I want to be able to check in cell a1 to see if it is currently bolded. If it is bolded then I want to output an x in cell y1. Move onto y2, if a2 is currently bolded, output an x in cell y2. I don't want to just offset it every time because I want to be able to use it for whichever column I currently have selected. If I can figure out how to create a range based on the active cell selected then I think I should be good

"Q1: Do you mean the cell in column A of the ActiveCell's row?"
Yes
"Q2: Range B:C is a little over 2 million cells. Do you want an "X" in all 2M, or do you mean an "X" in column B and column of the row that the active cell is in?"
I want to output x in the corresponding row of the column I choose to run the macro in, hence the relative referencing with activecell

The 83 cell number is just the about of things I need to check on the worksheet.

[Paul_Hossler]

Then maybe something like this

I think you wanted .Resize, not .Offset

Option Explicit
Sub xmarksthespot_guess_4()
    Dim C As Range, R As Range
    
    Set R = ActiveCell.Resize(83, 1)
     
    For Each C In R.Cells
        With C
            If .EntireRow.Cells(1).Font.Bold Then .Value = "X"
        End With
    Next
End Sub

if the ActiveCell is Y1, then ActiveCell.Resize(83,1) returns a range = Y1:Y83

Using C for each of the 83 cells in Y1:Y83

The .EntireRow = 1:1 (for Y1), and the .EntireRow.Cells(1) is A1


but ...

if the ActiveCell is Q101, then ActiveCell.Resize(83,1) returns a range = Q101:Q183

Using C for each of the 83 cells in Q101:Q183

The .EntireRow = 1:1 (for Q101), and the .EntireRow.Cells(1) is A101

[jdautel]

Boom, works like a charm. Thank you. I've never even seen the resize function. Very useful. This is my first programming language and started about 3 weeks ago so it's all very new to me still.

Already automated some tasks that were taking people hours in the office though. It's awesome stuff.

[SamT]

Click on the 6 pointed star below one of Paul's posts.