Storing Cell Value into a Variable

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
86
Office Version
  1. 365
Platform
  1. Windows
I hope this is not to hard.

I am trying to take an active cell value and place it in a memory as "USERS1" Variable.

Then, once its stored I am passing it to another program like autocad, so it would place that value in the command line.

VBA Code:
Public Sub PasteCurrentCell()

    Dim sh As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set sh = GetExcelSheet()
    If sh Is Nothing Then
        MsgBox "Excel is not running, or" & vbCrLf & _
        "opened Excel file does not have ""SHEET1""."
        Exit Sub
    End If
    
    Set rng = sh.Range("A2") '<<<<<---- How can I make this select the current active cell?? it could be in any column or row.
    rng.Copy
    
    InsertCurrentCellValue

End Sub

Private Function GetExcelSheet() As Excel.Worksheet

    Dim theSheet As Excel.Worksheet
    Dim sh As Excel.Worksheet
    Dim xls As Excel.Application
    
    On Error Resume Next
    
    Set xls = GetObject(, "Excel.Application")
    If Not xls Is Nothing Then
    
        For Each sh In xls.ActiveWorkbook.Worksheets
            If UCase(sh.Name) = "SHEET1" Then
                Set xls.ActiveSheet = sh
                Set theSheet = sh
                Exit For
            End If
        Next
        
    End If
    
    Set GetExcelSheet = theSheet
    
End Function

Private Sub InsertCurrentCellValue()
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
    ThisDrawing.SetVariable "USERS1", strcname '<<<<<------ Can I get the Current Active Cell to be stored in a "USERS1" Varible?
    AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
    
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,430
Office Version
  1. 365
Platform
  1. Windows
If you simply want to make "rng" the current active cell, just change this line:
VBA Code:
Set rng = sh.Range("A2")
to this:
VBA Code:
Set rng = ActiveCell

If you want to pass values from one procedure to the next, you will either need to set-up input arguments into the procedures you want to receive the passed value, or use Global/Public variables.
 

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
86
Office Version
  1. 365
Platform
  1. Windows
If you simply want to make "rng" the current active cell, just change this line:
VBA Code:
Set rng = sh.Range("A2")
to this:
VBA Code:
Set rng = ActiveCell

If you want to pass values from one procedure to the next, you will either need to set-up input arguments into the procedures you want to receive the passed value, or use Global/Public variables.
Thank you for that piece! Hit a wall trying to figure it out.
 

Forum statistics

Threads
1,175,516
Messages
5,897,891
Members
434,685
Latest member
asdf12l3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top