Offset Reference

Hi,

Simple question.

Say my ActiveCell is "D5", and I want to pull the value of same row but column B. Now I know I could do 'ActiveCell.Offset(0, -2).Value'. But how do I reference column B regardless of where my ActiveCell is??

For instance I will be in "D5", then maybe "S6", and so on. I always want to get the value of the same row as ActiveCell but in Column B.

Any ideas??

Thanks,
Matt

You might be able to use this kind of reference:

Range("B" & ActiveCell.Row).value

Does that help?

Ron

Ron,

Thanks a ton!! That was perfect!

So not to over stay my welcome, but how do I tell a module statement not to run IF XYZ() macro runs?? Does that make sense?

Thanks a million!
Matt

Ron, sounds good to me.

But I am not sure how to work it into my program, below you will see my code...maybe you can make a suggestion?


Dim vOldVal 'Must be at top of module



Private Sub Worksheet_Change(ByVal Target As Range)
Dim blnXYZ_Ran As Boolean

Dim bBold As Boolean
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet2
'.Cells(2, 1) = Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
'"short date")
.Cells(1, 2) = "CELL CHANGED"
.Cells(65536, 2).End(xlUp)(2, 1) = Target.Address
.Cells(1, 3) = "OLD VALUE"
.Cells(65536, 3).End(xlUp)(2, 1) = vOldVal
With .Cells(1, 4)
.Value = "NEW VALUE"
.ClearComments
.AddComment.Text Text:= _
"Matt Kleinke:" & Chr(10) & "" & Chr(10) & _
"Bold values are the results of formulas"
End With
With .Cells(65536, 4).End(xlUp)(2, 1)
.Value = Target
.Font.Bold = bBold
End With
.Cells(1, 1) = "ENTERED DP"
.Cells(65536, 1).End(xlUp)(2, 1) = "No"
.Cells(1, 5) = "TIME OF CHANGE"
.Cells(65536, 5).End(xlUp)(2, 1) = Time
.Cells(1, 6) = "DATE OF CHANGE"
.Cells(65536, 6).End(xlUp)(2, 1) = Date
.Cells(1, 7) = "CHANGED BY"
.Cells(65536, 7).End(xlUp)(2, 1) = Application.UserName
.Cells.Columns.AutoFit
.Cells(1, 8) = "Customer"
.Cells(65536, 8).End(xlUp)(2, 1) = Range("C" & ActiveCell.Row).Value
.Cells(1, 9) = "Location"
.Cells(65536, 9).End(xlUp)(2, 1) = Range("D" & ActiveCell.Row).Value
.Cells(1, 10) = "Nick Name"
.Cells(65536, 10).End(xlUp)(2, 1) = Range("E" & ActiveCell.Row).Value
.Cells(1, 11) = "Fab Location"
.Cells(65536, 11).End(xlUp)(2, 1) = Range("K" & ActiveCell.Row).Value
End With




vOldVal = vbNullString
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
vOldVal = Target
End Sub

You'd need to define the blnXYZ_Ran flag at the top of a module this way (not within a procedure):

Option Explicit
Public blnXYZ_Ran as Boolean

That way it's scope will be global and it's value will be persistent.

I hope that helps.

Ron

Ron,

Unfortunately I am not much of a programer, so I am running into errors.

Before I can even get to the portion of ending module code from running, I have to figure out how to assign a value to a cell (like "B", ActiveCell.Row) if one doesn't exist.

Any ideas on that??

Thanks so much for your help!
Matt

Well, here's a little bit of code that just sets the value of the cell in column B of the active cell's row to 1000:

Option Explicit

Sub setMyNumber()
Range("B" & ActiveCell.Row).Value = 1000
End Sub

There are many ways to approach setting cell values. I hope you've got a VBA book to help you along with your project.

Good luck and have fun learning.

Ron

Ron, that works perfectly!!

Thank you so much!!

Your approach really makes good sense!

But I still can't seem to get the code to stop when I execute XZY macro. The macro is initiated by a command button on sheet 1.

You're a great help Ron...I can't thank you enough!

Cheers,
Matt

1)Add a new VBA Module
2)Enter the following:
Option Explicit
Public blnXYZ_Ran As Boolean

3)Initialize that variable at some point in your code (or in an Open event):
blnXYZ_Ran = FALSE

4)The last step in the XYZ code should be this line:
blnXYZ_Ran = TRUE

Now you just need to check if blnXYZ_Ran =FALSE before executing any conditional code:

....other code
if blnXYZ_Ran = FALSE Then
....run your code
end if
....other code


Does that help?

Ron

Hey Ron,

I am slowly making great progress with this code. Here is the problem.

Now that I get the code to stop when I execute the macro, I can't get it to turn back on...Since the code is in the Sheet module and not a standalone module it doesn't have an execute.

Basically, I just don't want my code to track my changes when I execute my macro....

I tried your above solution, but maybe I am not using it right.

Thanks for your continued help!!

Matt

Well....Since I think you're running your XYZ code to stop the tracking, maybe you need an XYZ_Off macro that just sets the blnXYZ_Ran variable back to FALSE to re-engage tracking:

Sub XYZ_Off()
blnXYZ_Ran = FALSE
End Sub

Is that something you could use?

Ron