PDA

View Full Version : Help With For Loop



jdautel
08-15-2016, 09:31 AM
Ok, so I'm really new to VBA (about 3 weeks). And am just coding something simple to make a task easier here in the office. For some reason I'm running into a problem with my code though. Here's the code:


Sub callargument(x As Double)
Dim range1 As Range
Dim range2 As Range

Set range1 = Range("A55:A150")

Dim cell As Object
Dim cell2 As Object

Set range2 = Range("B1:B52")
Dim currentcell As Range

'Now I search in range 1 for whatever value it is that I need using a for loop
For Each cell In range1
If cell.Value = x Then

'if value is found then I want to bold the value name (hence the offset) in the above range 2
For Each cell2 In range2
If cell.Value = x Then
'searches for value

Set currentcell = Range(cell.Address)
currentcell.Select
Selection.Offset(-1, 0).Font.Bold = True
'this should bold the value of the cell to the left of the selected cell in the B range

End If
Next
End If
Next
End Sub


For some reason it is bolding the cells in range1 rather than range2 and I don't know why it would be doing that. Sorry if this is a really simple question but I've been playing with it for about 30 minutes and am at a loss..

jdautel
08-15-2016, 09:53 AM
Also, I know I can use the vlookup function to accomplish a similar task but I'm trying to use vba whenever I can to get better at it.

SamT
08-15-2016, 10:08 AM
Note Comma usage below
in re:
Selection.Offset(-1, 0)
Offset(-1) is the previous Row
Offset(, -1) is the previous Column
Note: Offset(RowCount, ColumnCount)

Use Ctrl+H to rename all instances of "cell" to "cell1". Insure that Whole Word is selected in the Replace dialog

You are using "cell" when you should be using "Cell2". renaming "cell" will help you keep them straight.



Set currentcell = Range(cell.Address)
Is the same as

Set currentcell = cell


Set currentcell = Range(cell.Address)
currentcell.Select
Selection.Offset(-1, 0).Font.Bold = True

Is the same as

cell.Offset(-1, 0).Font.Bold = True

________________________________________________


For Each cell2 In range2
If cell2 = cell1 Then cell2.Offset(,-1).Font.Bold = True
Next

Paul_Hossler
08-15-2016, 10:14 AM
Not tested, but some changes and comments marked with ------------------------




Option Explicit ' always a good idea -----------------

Sub callargument(x As Double)
Dim range1 As Range
Dim range2 As Range
Dim cell1 As Range ' made Range and changed name for consistency -------------
Dim cell2 As Range

Set range1 = Range("A55:A150")
Set range2 = Range("B1:B52")

'Now I search in range 1 for whatever value it is that I need using a for loop
For Each cell1 In range1.Cells

If cell1.Value = x Then

'if value is found then I want to bold the value name (hence the offset) in the above range 2
For Each cell2 In range2.Cells

'searches for value
If cell2.Value = x Then ' should be cell2------------------

'this should bold the value of the cell to the left of the selected cell in the B range

'-------------- .Offset(row, col) so (0, -1) = same row, one col to left -----------------
cell2.Offset(0, -1).Font.Bold = True ' no need to select - just act on the object
End If

Next
End If
Next
End Sub

jdautel
08-15-2016, 10:28 AM
Awesome. thank you both! Got it working.