Consulting

Results 1 to 7 of 7

Thread: import cell value ( area ) to VBA.

  1. #1

    import cell value ( area ) to VBA.

    Hello.

    i'm looking to get my macro to select an area based on 2 cells values.

    for examlple:
    i want my macro to look at I3 & I4 and then select the area described in these cells, so if the contents in I3 & I4 is A1 and C46 then the macro will select A1:46.

    see picture for reference.
    Attached Images Attached Images
    Last edited by technician12; 05-21-2019 at 05:14 AM. Reason: Changed picture for clarification

  2. #2
    snippet of current code:
        Dim Source As Range
        Dim Dest As Workbook
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim OutApp As Object
        Dim OutMail As Object
        Set Source = Nothing
        On Error Resume Next
        Set Source = Range("want macro to set area specified in I3 and I4 here, if possibe").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If Source Is Nothing Then
            MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
            Exit Sub
        End If
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Set wb = ActiveWorkbook
        Set Dest = Workbooks.Add(xlWBATWorksheet)

  3. #3
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Set Source = Range(Range("I3").Value, Range("I4").Value).SpecialCells(xlCellTypeVisible)
    Artik

  4. #4
    The source is not a range or the sheet is protected, please correct and try agein.

    i forgot to mention.
    the cells to call and get value from is on "Sheet2"
    sorry for the hassel.

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    So, you have active eg Sheet1 and now you want to get the values of I3 and I4 cells from Sheet2 and create a range reference in Sheet2. If so, then:
      With Worksheets("Sheet2") 
       Set Source = .Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
      End With
    Pay attention to the dots before Range.

    Artik

  6. #6
    Sorry, agein i misrepresented my wish
    range reference in sheet1

    sheet1 - the area to be selected
    sheet2 (I3 & I4 = the range to select on sheet1 )

    sorry for the misunderstanding

  7. #7
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    In that case
      With Worksheets("Sheet2") 
        Set Source = Worksheets("Sheet1").Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
      End With
    Gets values from Sheet2 and creates a range reference in Sheet1.

    Artik

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •