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
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.
Click on the 6 pointed star below one of Paul's posts.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.