Posts by bellevue

bellevue · Feb 2nd 2017

Re: Find solution to #VALUE! error message in SUMPRODUCT

Thanks ever so much NBVC, I feel such a wally as I thought I'd checked "simple" things like that but I obviously missed it.

It's been puzzling me for hours and I'm just so sorry there's no "technical learning" so to speak, to come from this for anyone else but I guess it reminds us all to check for bugs.

I've added to your reputation and want to say a huge thank you.

bellevue · Feb 2nd 2017

Hello

I have a macro enabled workbook with two worksheets and a #VALUE! error message appears in one column of formulae but not in another column that has the same formulae. To explain how the workbook works, basically one enters a start date (Sheet Availability D1) and a end date (Sheet Availability D2) for a 7 day period and then see the price for those 7 nights in Sheet A cells L13 and Y13 for two separate letting units.

I've attached a copy of the workbook: the issue appears in Worksheet A cells U7:U16 (encircled in a red box) using a SUMPRODUCT function. As far as I can see, the same formulae are used in Worksheet A cells H7:H16 (encircled in a blue box) and return a result without the error message.

In addition, when I look at a copy of the workbook, saved elsewhere, with the same formulae, both U7:U16 and H7:H16 work fine without an error message.

Can anyone explain what's going wrong please ?

Thanks

bellevue · Apr 3rd 2015

Re: Running a Macro from within another Macro

Hello

Thanks so much to "S O" for their help on this one and, when I got the solution from them, it all seemed to work.

However, when I get to the blank sheet in Workbook1, it opens Workbook2 and runs the macro for Workbook2 but it doesn't close Workbook1 ?

Where have I gone wrong please ? Any help would be greatly appreciated.

Thanks

PS - I'm aware of the change in file format when it saves

Code

Sub CreateUploadSheetA()
'
' CreateUploadSheetA Macro
'


'
    Application.DisplayAlerts = False
    Range("A1:G1001").Select
    Do Until IsEmpty(ActiveCell)
    If IsEmpty(ActiveCell) Then ThisWorkbook.Close
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=2, Criteria1:="<>"
    Selection.Copy
    Workbooks.Add
    Columns("A:G").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Dim FName           As String
    Dim FPath           As String
    FName = Range("A2")
    FPath = "H:\*****\*****\Upload Sheets"
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".xlsx", _
    FileFormat:=51, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    ActiveSheet.Next.Select
    Loop
    Workbooks.Open Filename:="H:\*****\*****\Upload Sheets\Workbook2.xlsm"
    Windows("Workbook2.xlsm").Activate
    Application.Run "'Workbook2.xlsm'!CreateUploadSheetH"
    Windows("Workbook1.xlsm").Activate
    ActiveWorkbook.Close
End Sub

Display More

bellevue · Apr 2nd 2015

Re: Running a Macro from within another Macro

Thanks so much, something so simple and I missed it.

Bless you

bellevue · Apr 1st 2015

I hope someone can help me please.

I have created a macro ("CreateUploadSheetA" - code below) which runs within a workbook called "Workbook1.xlsm".

It performs the macro in a loop through all the worksheets in this workbook until it encounters a blank worksheet.

At this point, it opens another workbook ("Workbook2.xlsm") and activates it and I've coded it to run a macro called "CreateUploadSheetH" before returning to Workbook1.xlsm and then closing that workbook.

However, as soon as the macro reaches the line to run the macro called "CreateUploadSheetH", it stops and returns the error message "Cannot run the macro 'CreateUploadSheetH'. The macro may not be available in this workbook or all macros may be disabled".

I've been into Windows Button > Excel Options > Trust Center > Trust Center Settings > Macro Settings and "Enable all macros" is selected.

In addition, the macro "CreateUploadSheetH" does exist (and thus is available...I think ?) within Workbook2.xlsm (the "active" workbook...I think) but it is not available within Workbook1.xlsm...not sure if that makes a difference.

Can anyone help please ?

Code

Sub CreateUploadSheetA()
'
' CreateUploadSheetA Macro
'


'
    Application.DisplayAlerts = False
    Range("A1:G1001").Select
    Do Until IsEmpty(ActiveCell)
    If IsEmpty(ActiveCell) Then ThisWorkbook.Close
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=2, Criteria1:="<>"
    Selection.Copy
    Workbooks.Add
    Columns("A:G").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Dim FName           As String
    Dim FPath           As String
    FName = Range("A2")
    FPath = "H:\*****\*****\Upload Sheets" 
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".txt", _
    FileFormat:=-4158, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    ActiveSheet.Next.Select
    Loop
    Workbooks.Open Filename:="H:\*****\*****\Workbook2.xlsm"
    Windows("Workbook2.xlsm").Activate
    Application.Run ("CreateUploadSheetH")
    Windows("Workbook1.xlsm").Activate
    ActiveWorkbook.Close
End Sub

Display More

bellevue · Apr 1st 2015

Re: Closing a workbook from within a macro

Roy

Thank you so much, you're an absolute star !

Is there a way of saying thank you (e.g. by adding to your reputation/rating) ?

Thanks again

bellevue · Mar 31st 2015

Re: Closing a workbook from within a macro

Thanks Roy

So, from the active sheet, what would I need to code to close the workbook and where would I need to put that please ?

Best wishes

bellevue · Mar 31st 2015

Hello

I hope someone can help please.

I have created a macro that runs through a series of worksheets in a workbook using a loop and performs calculations, the last of the worksheets is blank; I have set it such that when it comes across an empty cell (e.g. an empty cell within the last worksheet) it should stop and close the workbook...however, it's not working.

It gets to the last worksheet and stops but doesn't close the workbook.

Can anyone let me know please where I'm going wrong ?

Thanks

Code

Sub CreateUploadSheetA()
'
' CreateUploadSheetA Macro
'


'
    Application.DisplayAlerts = False
    Range("A1:G1001").Select
    Do Until IsEmpty(ActiveCell)
    If IsEmpty(ActiveCell) Then ThisWorkbook.Close
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$1000").AutoFilter Field:=2, Criteria1:="<>"
    Selection.Copy
    Workbooks.Add
    Columns("A:G").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Dim FName           As String
    Dim FPath           As String
    FName = Range("A2")
    FPath = "H:\*****\*****\Upload Sheets"
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".txt", _
    FileFormat:=-4158, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    ActiveSheet.Next.Select
    Loop
End Sub

Display More

bellevue · Mar 31st 2015

Re: Macro - Code to open windows side by side

Can anyone help please ?

bellevue · Mar 16th 2015

Hello

I have created a macro in excel 2007 that (in this order) opens a word document and then a web page: at the end of the macro (as it currently stands) I have the web page visible on my screen with the excel spreadsheet and word icons visible in the taskbar.

What I would like to do is add some code to the end of the macro that will then "snap" the three windows side by side (i.e. effectively right click the taskbar and select the "Show windows side by side" option).

The code, so far is:

Code

Sub Na02d()
    ActiveWorkbook.FollowHyperlink _
      Address:="H:\*****\Handling\*****\Gold\2015PriceGrid.docx", _
      NewWindow:=True
    ActiveWorkbook.FollowHyperlink _
      Address:="http://www.*****.com/to-let-userid***.html#availabilityAnchor", _
      NewWindow:=False
End Sub

I've used asterisks where personal information is visible.

I hope someone can help please.

Best wishes

bellevue · Nov 10th 2014

Re: Looking up data from a table

Hello Derk

Thanks ever so much for coming back to me.

There is some flexibility in the layout of the table, as long as it looks up the days before arrival as suggested. I'm not 100% sure what a look up table but I guess that's fine as well.

I hope you can help.

Best wishes (and thanks)

bellevue · Nov 7th 2014

I hope someone can help please; I've attached a sample worksheet so you can see what I'm trying to do.

I have a calendar of arrival dates for a property (cells B10:B24 in the attached example) and, for each date, I want cells C10:C24 to hold data from a table within the worksheet (cells B4:I7 in the attached example) based upon:

the number of days before arrival (cells B3:I3 in the attached example) AND
the "code" for that arrival day (cells A4:A7 in the attached example)

Thus, if the arrival day is 11/11/14, today would be 4 days before arrival (11/11/14-7/11/14) and the code for the arrival day would be "2" (cell A13). When you look at the table (A2:I7) the number at the intersection of these two criteria and what would need to go into cell C13 would be "15" (from cell H6).

I'm struggling to find the formula to make this happen and hope someone can help please.

Thanks.

forum.ozgrid.com/index.php?attachment/63006/

bellevue · Nov 6th 2014

Re: Combining two IF statements

First of all, can I say thank you very much to you guys for coming back with a reply, I do appreciate it.

Well…NBVC…you’ve done it again !! Thank you so much for the solution, you have really saved me an awful lot of time…bless you !

Thanks also to Batman and MikeRickson, your solutions are good as well.

For Luke M, I think you may have misread the formula as the two statements aren’t exclusive…the first is calculating the product of Availability!$D$1:Availability!$D$1 whereas the second is calculating the product of Availability!$D$1:Availability!$D$2.

[FONT=&quot]Anyway, thanks to you for your contribution as well.[/FONT]

bellevue · Nov 5th 2014

I do so hope someone can help please ?

I have two IF statements (that each include SUMPRODUCT functions) and I need to combine them, potentially by using "IF(AND(..."

The two statements are:

=IF(SUMPRODUCT(--(C$18:C$873=Availability!$D$1),--(C$18:C$873=Availability!$D$1),--(A$18:A$873))=3,1,0)

[FONT=&quot]=IF(SUMPRODUCT(--(C$18:C$873=Availability!$D$1),--(C$18:C$873=Availability!$D$2),--(A$18:A$873))>198,1,0)

Ideally, the result if both statements are true should be "1" or "0" if either/both are false.

I'm not 100% sure if it's possible to combine two IF statements that include SUMPRODUCT but I'm sure someone will quickly tell me. I've tried to do it like this but to no avail:

=IF(AND(SUMPRODUCT(--(C$18:C$873=Availability!$D$1),--(C$18:C$873=Availability!$D$1),--(A$18:A$873))=3,IF(SUMPRODUCT(--(C$18:C$873=Availability!$D$1),--(C$18:C$873=Availability!$D$2),--(A$18:A$873))>198,1,0))

I hope someone can help.

Thanks[/FONT]

bellevue · Nov 3rd 2014

Re: Combining four IF statements

Thanks ever so much NBVC, that's brilliant.

Bless you !

bellevue · Nov 3rd 2014

Hello

I hope someone can help please. I have four IF statements that work fine on their own; however, I need to combine them into one statement. I have tried a lot of ways but can't seem to get it to work.

The four statements are below are I would really appreciate some help please.

Thanks

=IF(AND(Availability!$D$2>B$290,Availability!$D$1<B$297),IF(AND(Availability!$D$1<>B$290),IF(Availability!$D$2-Availability!$D$1=7,(C$14-(C$14*2)),0)))

=IF(AND(Availability!$D$2>B$346,Availability!$D$1<B$388),IF(AND(Availability!$D$1<>B$346,Availability!$D$1<>B$353,Availability!$D$1<>B$360,Availability!$D$1<>B$367,Availability!$D$1<>B$374,Availability!$D$1<>B$381),IF(Availability!$D$2-Availability!$D$1=7,(C$14-(C$14*2)),0)))

=IF(AND(Availability!$D$2>B$661,Availability!$D$1<B$668),IF(AND(Availability!$D$1<>B$661),IF(Availability!$D$2-Availability!$D$1=7,(C$14-(C$14*2)),0)))

=IF(AND(Availability!$D$2>B$717,Availability!$D$1<B$759),IF(AND(Availability!$D$1<>B$717,Availability!$D$1<>B$724,Availability!$D$1<>B$731,Availability!$D$1<>B$738,Availability!$D$1<>B$745,Availability!$D$1<>B$752),IF(Availability!$D$2-Availability!$D$1=7,(C$14-(C$14*2)),0)))

bellevue · Jan 27th 2013

Re: VBA Access/Word Mailmerge

Hello Mike

I know this thread is old but I'm looking for the same solution - did you ever get a solution ?

Thanks