none
Dget formula RRS feed

  • Question

  • i have this formula =DGET(CompanyM!A1:AE2402,'Info Sheet'!F226,'Info Sheet'!$E$226:$E$227) where AE represents the las row in the data string.

    The data always expends so I have a little macro running when I get to this sheet automatically runs and  finds the last row in a data string and puts the information (number) in cell U203 how can I replace the number next to AE with the number from Call U203



    Vforum

    Saturday, October 26, 2019 8:05 PM

Answers

  • Hi Kosta si,

    Sorry, I cannot understand what you want to do.
    But by referring my code, you could do what you want.

    Regards,

    Ashidacchi -- http://hokusosha.com

    • Marked as answer by Kosta si Sunday, October 27, 2019 7:30 PM
    Sunday, October 27, 2019 11:09 AM
  • ok i understand 

    so i created this

    in the sheet that the change is happening.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Sheets.Cells(1, "G") = ActiveCell Then
    Call company_address
    End If
    End Sub

    the problem is if any other cells in the sheet are changing or touched i am get this error

    what i need is the routine to work only if the particular cells information changes in this case is cell G1 

    thank you


    Vforum

    • Marked as answer by Kosta si Sunday, October 27, 2019 7:30 PM
    Sunday, October 27, 2019 12:01 PM

All replies

  • i have this formula =DGET(CompanyM!A1:AE2402,'Info Sheet'!F226,'Info Sheet'!$E$226:$E$227) where AE represents the las row in the data string.


    If the above formula is correct, or works fine, a macro would be like this.
    Private Sub Worksheet_Activate()
        ' --- get the last row in column [A] of sheet "CompanyM"
        Dim lastRow As Long
        lastRow = Sheets("CompanyM").Cells(Rows.Count, 1).End(xlUp).Row
        ' --- set DGET formula in [B1] of this sheet
        Range("B1").Value = _
            "=DGET(CompanyM!A1:AE" & lastRow & ",'Info Sheet'!F226,'Info Sheet'!$E$226:$E$227)"
    End Sub
    
    Regards,

    Ashidacchi -- http://hokusosha.com

    Sunday, October 27, 2019 3:42 AM
  • thank you

    so there is no way to do this as a formula not as VBA macro .

    if so then i need to change little bit the macro, should run every time the value in the cell E227 changes  


    Vforum

    Sunday, October 27, 2019 8:26 AM
  • Hi Kosta si,

    Sorry, I cannot understand what you want to do.
    But by referring my code, you could do what you want.

    Regards,

    Ashidacchi -- http://hokusosha.com

    • Marked as answer by Kosta si Sunday, October 27, 2019 7:30 PM
    Sunday, October 27, 2019 11:09 AM
  • ok i understand 

    so i created this

    in the sheet that the change is happening.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Sheets.Cells(1, "G") = ActiveCell Then
    Call company_address
    End If
    End Sub

    the problem is if any other cells in the sheet are changing or touched i am get this error

    what i need is the routine to work only if the particular cells information changes in this case is cell G1 

    thank you


    Vforum

    • Marked as answer by Kosta si Sunday, October 27, 2019 7:30 PM
    Sunday, October 27, 2019 12:01 PM