Prev: Self Signed Digital Certificates.
Next: Matching Data between 2 Tabs on Same Workbook - then calculating
From: OssieMac on 18 Mar 2010 02:27
Hi again Simon,

I think that the best way is to use named ranges rather than VBA variables.
Named ranges are as you would name a range on the worksheet. If you don't
know what they are then look up Defined Names in help.

These defined names get saved with the workbook. However, I don't think you
can do that without opening the workbook but I don't know any other way of
doing this so that you can use the defined names in the formula and I don't
know any other easy way of achieving what you want. If I were writing the
code I would do it entirely differently but I would need the workbooks to do
that.

Here is an example of just the first instance of finding the last cell in
the columns, defining a name and using the defined name in the formula. None
of it is tested.

If you copy your long line of code and paste it into you VBA editor then it
will probably mess up with breaks where it should not break and add double
quotes where it should not. Suggest you simply edit your code as per my
comments below.

I think I have got it right in that "Category by Customer - Excel Ex" is the
worksheet name.

'Lookup Current Month Sales
Range("J4").Select
With Workbooks("C:\ABB\SalesCurrentMonth.xls") _
.Sheets("Category by Customer - Excel Ex")

.Cells(.Rows.Count, 10) _
.End(xlUp).Name = "LastCol10"

.Cells(.Rows.Count, 10) _
.End(xlUp).Name = "LastCol14"

End With

'Now in your following line of code
'Edit R6C10:R263C10
'to R6C10:LastCol10

'Edit R6C14:R263C14
'to R6C14:lastCol14

Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesCurrentMonth.xls]Category by Customer - Excel
Ex '!R6C10:R263C10=RC1,'C:\ABB\[SalesCurrentMonth.xls]Category by Customer -
Excel Ex '!R6C14:R263C14,0))"

--
Regards,

OssieMac


From: OssieMac on 18 Mar 2010 16:24
Hi again Simon,

I did a little testing and my answer is not correct. You would have to
prefix the named range with the entire workbook reference for the formula to
work. Therefore the following method of naming the entire range to which the
formula is applied is better because the workbook only needs to be referenced
once. However, as I said before, the workbook needs to be open. (See
alternative method at bottom of this post.)

With Workbooks("C:\ABB\SalesCurrentMonth.xls") _
.Sheets("Category by Customer - Excel Ex")

'Name the range in Col 10 to use in the formula
..Range(.Cells(6, 10), .Cells(.Rows.Count, 10)) _
.End(xlUp).Name = "Col_10"

'Name the range in Col 14 to use in the formula
.Range(.Cells(6, 14), .Cells(.Rows.Count, 14)) _
.End(xlUp).Name = "Col_14"

End With

Now in your code with the formula
Edit R6C10:R263C10
to Col_10

Edit R6C14:R263C14
to Col_14

What you could do instead of opening each workbook for this particular code
is to place code in the WorkbookClose event of each of the referenced
workbooks and update the named ranges each time the workbook is closed. The
following code goes in ThisWorkbook module of each of the workbooks.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Edit sheet name if necessary
With .Sheets("Category by Customer - Excel Ex")
.Range(.Cells(6, 10), _
.Cells(.Rows.Count, 10) _
.End(xlUp)).Name = "Col_10"

.Range(.Cells(6, 14), _
.Cells(.Rows.Count, 14) _
.End(xlUp)).Name = "Col_14"
End With

End Sub

You will be prompted to save the workbook when closing because naming a
range is a change to the workbook and you want to save the defined name. If
you don't save then the named range is not updated. You could include code to
save the workbook automatically but it is dangerous if someone messes up with
editing the workbook and wants to close without the changes and start again
and automatic saving does not give them that option.

--
Regards,

OssieMac


First  |  Prev  | 
Pages: 1 2
Prev: Self Signed Digital Certificates.
Next: Matching Data between 2 Tabs on Same Workbook - then calculating