make an active cell show its reference 1

[allenEd]

Whats the VBA that makes an active cell show its reference. I am learning VBA, so sorry if this is a stupid question.

I have tried:


Dim AC As Long

Sheets("Corus - Kal Zip.&quot.Select
AC = ActiveCell.Value
ActiveCell = AC


End Sub

needless to sat it dosn't have the desired effect.
thanks

 

[Zathras]

Here is just about the simplest way:

Sub demo()
  MsgBox Selection.Address
End Sub

But, what are you really trying to do?

 

[allenEd]

Thanks for the help, I am trying to figure something, one bit at a time....
problem:....

I have been asked to sort a problem out for one of my customers who is a builder. I wonder if any one can help.

I have 2 work sheets, sheet 2 works out data and gives results in E60, E50 and E40 for one set of bulding supplies. And again in G60, G50 and G40 for another. This goes one, but the relationship between the actual cells (to look at)is the same.

Say I want to copy E60 into a cell in sheet1 (which I make active), how can I get E50, and E40 from sheet 2 to paste in other cells (all relative from the active cell) in sheet one.


The idea is he can paste data (E60) into the active
cell on sheet one, and the other cells E50 and E40 will follow. The relative relationship between the cells is allows the same.


hope that makes sence.
cheers

 

[Zathras]

Ok... I think I understand what you want...

Set up a blank workbook this way to test the macros listed below:

Create 2 sheets: Sheet1 and Sheet2

Enter the following macro code:

Sheet1:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Target.Count = 1 Then
    Selection1 = "Sheet1!" + Target.Address
  End If
End Sub

Sheet2:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Target.Count = 1 Then
    Selection2 = "Sheet2!" + Target.Address
  End If
End Sub

Insert a module:

Module1:

Option Explicit
Public Selection1 As String
Public Selection2 As String

Sub CopyThreeCells()
Dim FromCell As Range
Dim ToCell As Range
  Set FromCell = Range(Selection2)
  Set ToCell = Range(Selection1)
  ToCell.Value = FromCell.Value
  ToCell.Offset(-10, 0).Value = FromCell.Offset(-10, 0).Value
  ToCell.Offset(-20, 0).Value = FromCell.Offset(-20, 0).Value
  Set ToCell = Nothing
  Set FromCell = Nothing
End Sub

Select Sheet2 and enter the following three cells:

E40: 'This is from E40
E50: 'This is from E50
E60: 'This is from E60

Leave Sheet2 cell E60 selected.
Select Sheet1 and select any cell on line 60 or below.
Run the macro CopyThreeCells()

Obviously this would have to be beefed up with some error checking (proper row, only one cell selected, etc.) but I think it covers the essentials.

Let me know if this is what you want.

 

[allenEd]

wow, I've got a lot to learn. That worked great. Thanks for your help.

regards
Allen

 

[allenEd]

Hi Zathras

I have tried to transpose the sample sheet to my "working" sheet and get...
In Module 1
Runtime error 1004
"Method 'Range' of object '_Global' failed"
at line,
Set FromCell = Range("Selection2&quot

what have I done?

thanks for your help.

 

[allenEd]

My code is:

sheet2:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count = 1 Then
Selection1 = "sheet2!" + Target.Address
End If
End Sub


sheet6:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count = 1 Then
Selection2 = "sheet6!" + Target.Address
End If
End Sub

module1:
Option Explicit
Public Selection1 As String
Public Selection2 As String

Sub CopyThreeCells()
Dim FromCell As Range
Dim ToCell As Range
Set FromCell = Range("Selection2&quot
Set ToCell = Range("Selection1&quot
ToCell.Value = FromCell.Value

ToCell.Offset(0, 6).Value = FromCell.Offset(-6, 0).Value
ToCell.Offset(0, 2).Value = FromCell.Offset(-13, 0).Value
ToCell.Offset(0, 3).Value = FromCell.Offset(-14, 0).Value

Set ToCell = Nothing
Set FromCell = Nothing
End Sub

 

[Zathras]

The variables "Selection1" and "Selection2" are blank to begin with and only get set when in fact a single cell is selected on whatever sheet the Worksheet_SelectionChange event is coded for. Since I see references to "sheet2" and "sheet6" there are a couple of things that can go wrong:

The tabs may not have the correct labels of "sheet2" and "sheet6"

The user may not have selected single cells on each of the two sheets before trying to run the macro.

For a production version of the code, you need to test the variables to be sure they are not "" before continuing. If either shows up as "" then show a msgbox with an appropriate instruction, e.g.:

  If Selection1 = "" Then
    MsgBox "You need to select a target data cell on sheet2"
  End If