0

I am trying to run a "DCOUNT" formula in a cell in VBA but then my database range (in the formula) refers to a dynamic range on a different sheet. Now the challenge is:

how do I frame my VBA code to refer to the database (which would be dynamic but always starting from cell A1 with the same number of columns but the number of rows would change depending on the data copied in) in a sheet different from the sheet where the result of the formula is to appear?

Private Sub DeriveTheFigures_Click()
    Worksheets("see the figures.").Activate
    Dim fulldataset As Range
    Worksheets("paste trial billing here.").Range("a1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select                                                                                                   fulldataset= Selection.Address(rowabsolute:=False, columnabsolute:=False)
     Range("f9").FormulaR1C1 = "=DCOUNT("
 End Sub**

Useless picture of code

vacip
  • 5,246
  • 2
  • 26
  • 54
Julius Blue
  • 53
  • 1
  • 6
  • What does your code look like now? – Vegard Oct 12 '16 at 09:35
  • If you could just copy and paste the code that would be much easier, pics are a draaaaag – Preston Oct 12 '16 at 10:07
  • to find the last row you can use this: LastRow = .Range("E" & .Rows.Count).End(xlUp).Row – Preston Oct 12 '16 at 10:09
  • Private Sub DeriveTheFigures_Click() Worksheets("see the figures.").Activate Dim fulldataset As Range Worksheets("paste trial billing here.").Range("a1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select fulldataset = Selection.Address(rowabsolute:=False, columnabsolute:=False) Range("f9").FormulaR1C1 = "=DCOUNT(" End Sub the code so far. – Julius Blue Oct 12 '16 at 10:15
  • and what's the "E" for in the range address you are suggesting? remember i intend to adopt a r1c1 style formula or is there a more effective alternative? – Julius Blue Oct 12 '16 at 10:21
  • You say 'remember', but didn't put code in your question until after my comment... – Preston Oct 12 '16 at 10:30
  • you're right i'm sorry about that. – Julius Blue Oct 12 '16 at 10:35

0 Answers0