PDA

View Full Version : Basic VBA Help with Ifs, fors, and relative referencing



jdautel
08-16-2016, 06:36 AM
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
08-16-2016, 07:09 AM
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
08-16-2016, 07:51 AM
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
08-16-2016, 07:55 AM
How can I create a range that is based upon the active cell selected

Paul_Hossler
08-16-2016, 08:03 AM
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
08-16-2016, 08:25 AM
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
08-16-2016, 08:33 AM
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
08-16-2016, 08:50 AM
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
08-16-2016, 09:10 AM
Click on the 6 pointed star below one of Paul's posts.