How can I set a value in a cell to be range for a formula in vba excel?-CodePudding

I have code that extracts the cell.address given the value and the range initially, but then I want it to look for a new value in a new range is only in the cell.address row number. I have added comments in the code to explain it better but in cell j3 I get the cell address for e.g $A$15 and in cell k3 i can get the row number using left, right function "15" and I want the range in second "set cell" function to change depending on either of those cells so if the first output is $A$20, I want the second function row to change to 20

Sub find()
Dim a As Double
Dim wks As Worksheet
Dim b As Double
Dim c As Integer
Dim cell As Range


       
         Set wks = Worksheets("comefri")
            a = wks.Range("c8").value
            b = wks.Range("D7").value
            c = wks.Range("k4").value
            
       
 With comefri
       
      
Set cell = Range("a:a").find(b, MatchCase:=Fasle, searchformat:=False)
         Range("j3").value = cell.Address
               
               
  ' I want the range row number to change depending on the value output form cell j3 or k3
 Set cell = Range("CX15:GS15").find(a, MatchCase:=Fasle, searchformat:=False)
               Range("K3").value = cell.Address
     

 
Range("k3").value = cell.Address


             
             
             
        End With
                

   
                

End Sub

Public Function ToColNum(ColN)
    ToColNum = Range(ColN & 1).Column
    End Function


Function GetValue(row As Integer, col As Integer)
    GetValue = ActiveSheet.Cells(row, col)
End Function

CodePudding user response：

To set the value of a cell to be the range for a formula in VBA Excel, you can use the following code:

Sub set_range()
Dim cell As Range
Dim value As String
Dim formula As String

Copy code
' Set the cell to be the cell at row 3, column 5
Set cell = ActiveSheet.Cells(3, 5)

' Set the value to be the address of the cell (e.g. "E3")
value = cell.Address

' Set the formula to be the SUM function using the cell as the range
formula = "=SUM(" & value & ")"

' Set the formula of the cell to be the formula we defined
cell.Formula = formula
End Sub

Note that this code uses the SUM function as an example, but you can substitute it with any formula that uses a range.

CodePudding user response：

Finding Cells Using the Find Method

Option Explicit

Sub FindValue()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("comefri")
       
    With ws
        
        Dim cellB As Range: Set cellB = .Range("J3")
        Dim cellA As Range: Set cellA = .Range("K3")
        
        Dim B As Double: B = .Range("D7").Value
        Dim A As Double: A = .Range("C8").Value
        'Dim C As Double: C = .Range("K4").Value ' not used at the moment
        
        Dim cell As Range
              
        With .Range("A:A") ' Reference the search column range.
            Set cell = .Find(B, .Cells(.Cells.Count), xlValues, xlWhole)
        End With
        
        If cell Is Nothing Then ' 'B' not found
            cellB.ClearContents
            cellA.ClearContents
            Exit Sub
        End If
            
        cellB.Value = cell.Address
        
        Dim FoundRow As Long: FoundRow = cell.Row
        
        With .Range("CX:GS").Rows(FoundRow) ' Reference the search row range.
            Set cell = .Find(A, .Cells(.Cells.Count), xlValues, xlWhole)
        End With
                       
        If cell Is Nothing Then cellA.ClearContents: Exit Sub ' 'A' not found
        
        cellA.Value = cell.Address
                       
    End With
    
End Sub