Logic Erroe in VBA Code Attached

The following code works except for the IF clause.

The idea is to select a range of cells on the active worksheet.

Those selected cells contain formulas that are to be encapsulated by another
formula.

Using the VBA Debugger in Excel 2003, I noted that "Range" in the Dim
statement is not in Blue print as are the others  like "String."

The point of my concern is that the value for myCellRange is "Nothing"
according to the debugger.

Therefore, the MsgBox and Exit occur.

If I comment out the If clause, the Macro works unless I do not select cells
with formulas in them.

My idea was to trap that error with the IF clause.

Why is the value of myCellRange always "Nothing" even when cells are selected?

How can I trap the non selection of cells error yet still have the macro work?

TIA Dennis

Sub ReplaceFormula()

    Dim CellFormula As String
    Dim myCellRange As Range
    Dim Placeholder As String
    Dim NewFormula As String
    If myCellRange Is Nothing Then MsgBox "No Cells were selected!"
    If myCellRange Is Nothing Then
        Exit Sub
    Else
       NewFormula = Application.InputBox("Enter the New Formula _
              with a Placeholder  for the Cell Formula. _
              (Start with a single quote like '=X+Y)")
        Placeholder = Application.InputBox("What variable in the New _
              Formula, entered in the previous Screen, is to be replaced _
              with Cell Formula?")
        For Each myCellRange In Intersect(Selection, _
                 Selection.SpecialCells(xlCellTypeFormulas))
              CellFormula = Mid(myCellRange.Formula, 2, _
                 Len(myCellRange.Formula)) _
                myCellRange.Formula = Mid(Replace(NewFormula, _
                Placeholder, CellFormula), 2)
        Next myCellRange
    End If
    
End Sub
0
1/30/2004 5:23:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
410 Views

Similar Articles

[PageSpeed] 47

Dennis,

When you "Dim" a range object, you are simply reserving room for it.
You must then "Set" the range so Excel knows what it is.
In a loop, the "setting" is automatically done by the Excel.

Input boxes come in two flavors:
   Application.InputBox - can return a range object
   InputBox - returns a string

If the user cancels the Input Box, you must have some way to
   exit the sub.
Each line of text in an InputBox must be enclosed
with quote marks.

The following modified code should get you going.
The formula replace section may need some additional work.
'--------------------------------------------------------------
Sub ReplaceFormula()

 Dim MyCell          As Range
 Dim CellRange      As Range
 Dim CellFormula   As String
 Dim Placeholder    As String
 Dim NewFormula  As String

 Set CellRange = Selection
 If CellRange Is Nothing Then
    MsgBox "No Cells were selected!"
    Exit Sub
 Else
    NewFormula = InputBox("Enter the New Formula" & vbNewLine & _
                 "with a Placeholder  for the Cell Formula." & vbNewLine & _
                 "( Start with a single quote like =X+Y) )", " Blame Dennis")
    If Len(NewFormula) = 0 Then
       Set CellRange = Nothing
       Exit Sub
    Else
       Placeholder = InputBox("What variable in the New Formula," & vbNewLine &
_
               "entered in the previous Screen," & vbNewLine & _
               "is to be replaced with Cell Formula?", " Blame Dennis")
    End If
    If Len(Placeholder) = 0 Then
       Set CellRange = Nothing
       Exit Sub
    Else
       For Each MyCell In CellRange.SpecialCells(xlCellTypeFormulas)
           CellFormula = Mid(MyCell.Formula, 2, Len(MyCell.Formula))
           MyCell.Formula = Mid(Replace(NewFormula, Placeholder, CellFormula),
2)
       Next 'MyCell
    End If
 End If

Set MyCell = Nothing
Set CellRange = Nothing
End Sub
'-------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA

"Dennis" <dmburgess@removespamameritech.net> wrote in message
news:9m3l10t5uvhm38n2h3l9gbh8v8f60ab4jb@4ax.com...
> The following code works except for the IF clause.
> The idea is to select a range of cells on the active worksheet.
> Those selected cells contain formulas that are to be encapsulated by another
> formula.
> Using the VBA Debugger in Excel 2003, I noted that "Range" in the Dim
> statement is not in Blue print as are the others  like "String."
> The point of my concern is that the value for myCellRange is "Nothing"
> according to the debugger.
> Therefore, the MsgBox and Exit occur.
> If I comment out the If clause, the Macro works unless I do not select cells
> with formulas in them.
> My idea was to trap that error with the IF clause.
> Why is the value of myCellRange always "Nothing" even when cells are selected?
> How can I trap the non selection of cells error yet still have the macro work?
> TIA Dennis

> Sub ReplaceFormula()
>     Dim CellFormula As String
>     Dim myCellRange As Range
>     Dim Placeholder As String
>     Dim NewFormula As String
>     If myCellRange Is Nothing Then MsgBox "No Cells were selected!"
>     If myCellRange Is Nothing Then
>         Exit Sub
>     Else
>        NewFormula = Application.InputBox("Enter the New Formula _
>               with a Placeholder  for the Cell Formula. _
>               (Start with a single quote like '=X+Y)")
>         Placeholder = Application.InputBox("What variable in the New _
>               Formula, entered in the previous Screen, is to be replaced _
>               with Cell Formula?")
>         For Each myCellRange In Intersect(Selection, _
>                  Selection.SpecialCells(xlCellTypeFormulas))
>               CellFormula = Mid(myCellRange.Formula, 2, _
>                  Len(myCellRange.Formula)) _
>                 myCellRange.Formula = Mid(Replace(NewFormula, _
>                 Placeholder, CellFormula), 2)
>         Next myCellRange
>     End If
> End Sub


0
jim.coneXXX (771)
1/30/2004 6:34:06 PM
Jim thanks for your time - especially in explaining the underling logic.

Dennis



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote:

>Dennis,
>
>When you "Dim" a range object, you are simply reserving room for it.
>You must then "Set" the range so Excel knows what it is.
>In a loop, the "setting" is automatically done by the Excel.
>
>Input boxes come in two flavors:
>   Application.InputBox - can return a range object
>   InputBox - returns a string
>
>If the user cancels the Input Box, you must have some way to
>   exit the sub.
>Each line of text in an InputBox must be enclosed
>with quote marks.
>
>The following modified code should get you going.
>The formula replace section may need some additional work.
>'--------------------------------------------------------------
>Sub ReplaceFormula()
>
> Dim MyCell          As Range
> Dim CellRange      As Range
> Dim CellFormula   As String
> Dim Placeholder    As String
> Dim NewFormula  As String
>
> Set CellRange = Selection
> If CellRange Is Nothing Then
>    MsgBox "No Cells were selected!"
>    Exit Sub
> Else
>    NewFormula = InputBox("Enter the New Formula" & vbNewLine & _
>                 "with a Placeholder  for the Cell Formula." & vbNewLine & _
>                 "( Start with a single quote like =X+Y) )", " Blame Dennis")
>    If Len(NewFormula) = 0 Then
>       Set CellRange = Nothing
>       Exit Sub
>    Else
>       Placeholder = InputBox("What variable in the New Formula," & vbNewLine &
>_
>               "entered in the previous Screen," & vbNewLine & _
>               "is to be replaced with Cell Formula?", " Blame Dennis")
>    End If
>    If Len(Placeholder) = 0 Then
>       Set CellRange = Nothing
>       Exit Sub
>    Else
>       For Each MyCell In CellRange.SpecialCells(xlCellTypeFormulas)
>           CellFormula = Mid(MyCell.Formula, 2, Len(MyCell.Formula))
>           MyCell.Formula = Mid(Replace(NewFormula, Placeholder, CellFormula),
>2)
>       Next 'MyCell
>    End If
> End If
>
>Set MyCell = Nothing
>Set CellRange = Nothing
>End Sub
>'-------------------------------------------------------------
>
>Regards,
>Jim Cone
>San Francisco, CA
>
>"Dennis" <dmburgess@removespamameritech.net> wrote in message
>news:9m3l10t5uvhm38n2h3l9gbh8v8f60ab4jb@4ax.com...
>> The following code works except for the IF clause.
>> The idea is to select a range of cells on the active worksheet.
>> Those selected cells contain formulas that are to be encapsulated by another
>> formula.
>> Using the VBA Debugger in Excel 2003, I noted that "Range" in the Dim
>> statement is not in Blue print as are the others  like "String."
>> The point of my concern is that the value for myCellRange is "Nothing"
>> according to the debugger.
>> Therefore, the MsgBox and Exit occur.
>> If I comment out the If clause, the Macro works unless I do not select cells
>> with formulas in them.
>> My idea was to trap that error with the IF clause.
>> Why is the value of myCellRange always "Nothing" even when cells are selected?
>> How can I trap the non selection of cells error yet still have the macro work?
>> TIA Dennis
>
>> Sub ReplaceFormula()
>>     Dim CellFormula As String
>>     Dim myCellRange As Range
>>     Dim Placeholder As String
>>     Dim NewFormula As String
>>     If myCellRange Is Nothing Then MsgBox "No Cells were selected!"
>>     If myCellRange Is Nothing Then
>>         Exit Sub
>>     Else
>>        NewFormula = Application.InputBox("Enter the New Formula _
>>               with a Placeholder  for the Cell Formula. _
>>               (Start with a single quote like '=X+Y)")
>>         Placeholder = Application.InputBox("What variable in the New _
>>               Formula, entered in the previous Screen, is to be replaced _
>>               with Cell Formula?")
>>         For Each myCellRange In Intersect(Selection, _
>>                  Selection.SpecialCells(xlCellTypeFormulas))
>>               CellFormula = Mid(myCellRange.Formula, 2, _
>>                  Len(myCellRange.Formula)) _
>>                 myCellRange.Formula = Mid(Replace(NewFormula, _
>>                 Placeholder, CellFormula), 2)
>>         Next myCellRange
>>     End If
>> End Sub
>

0
1/30/2004 9:04:19 PM
Reply:

Similar Artilces:

attachments #14
When opening email attachments a box should pop up asking if you would like to open the attachment or save the attachment. Down the bottom there is a tick box which you can tick so that attachments will automatically open and it will no longer ask you. Well i have accidentally ticked the box and want to get it back.I cannot for the life of me work out how to do it? Does anyone have any idea? Thanks Aly ...

Country Codes #2
I can't for the life of me find the GP menu item that maintains the codes. Can anyone point me to it? Open the Country Code Maintenance window. (Tools >> Setup >> Company >> Country Codes) The Country Code Maintenance window will be available only if you have marked to enable Intrastat tracking in the Company Setup Options window. See Help for more info -- Elisabeth "Ken Denman" wrote: > I can't for the life of me find the GP menu item that maintains the codes. > Can anyone point me to it? If you don't have Intrastat Tracking turned on ...

difficulty opening e-mail attachments
I am unable to open attachments sent to me in my e-mail. Outlook Express indicates the attachments are "unsafe"and prevents me from opening them. Many of these attachments are sent to me by friends and I am sure there are no viruses associated with them. I have been unable to find out how to change any of the security settings within O.E. Does anyone out there have any advice? Try this site for information on removing the block: http://www.omegageek.com/OESecurity.htm This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Int...

Have to scroll to see full list of attachments in Outlook 2007?
A colleague of mine keeps sending me HTML emails in Outlook with multiple attachments, but the full list of attachments does not appear all at once on the screen. There is a scroll bar on the FAR right next to the attachments, and you have to scroll down through the list of attachments to see all of the attachments (I can send a screen shot to better explain this). So for example, if an email has four attachments, I only see the first three on the screen, and then I have to scroll down to see the fourth attachment. This is very confusing as I can easily "miss" an attach...

Attachment guideline or limit for lists
Can anyone tell me the guideline or limit that Microsoft recommends for attachments to a list iitem. I know there is a 2,000 guideline for number of items in a list. Is there any documentation from Microsoft that shows how adding attachments to lists degrades performance for viewing and searching in SharePoint? Thanks for any help on this. One slight correction... it's 2000 items in a view, not a list. You can have thousands of items in the list. Just try and keep your views to less than 2000. -- Daniel A. Galant Imagine what we could be... if we could just imagine...

Colour code messages only to me
Hi I am running Outlook XP. There is a rule that can be set to colour code emails that are sent only to me, but what it actually does is colour code emails that only have me in the To: line of the email. If an email is sent to me and CC's in someone else, the rule treats this as a mail sent only to me. How do I set the rule so that it colour codes a message sent solely to me? Thanks in advance ...

Secure code in Excel & Power Point ADD-INS
Is there any way to secure code in Excel & Power Point ADD-INS, except the password defined in VBA ? No. Use VB to create COM addins. ************ Anne Troy VBA Project Manager www.OfficeArticles.com <CLarkou@gmail.com> wrote in message news:1132296881.379001.293560@o13g2000cwo.googlegroups.com... > Is there any way to secure code in Excel & Power Point ADD-INS, except > the password defined in VBA ? > Thanks a lot, these add-ins can be called in Access also, isn't? The COM add-ins can be created by Visual Basic code or it needs C++ ? You can use VB. -- ...

color coding events
I would like to color code events on my daily calendar and wonder if there is a way to change the color of text on individual events On 3/7/2010 6:27 AM, jcurtis wrote: > I would like to color code events on my daily calendar and wonder if > there is a way to change the color of text on individual events Which version of Outlook? Outlook 2007 introduced colored categories and I use them daily for color-coding events. Prior to Outlook 2007, colored labels were your option: http://office.microsoft.com/en-us/outlook/HP030848441033.aspx Additional information: htt...

Automatic Insert Rows VBA
A Very Good Evening All, I have a macro that runs: advanced filter/copy to another place/unique, between two worksheets (Column A in each worksheet being the identfier; worksheet1 is source data,with duplicates of identifiers; worksheet2 is formula driven from the sum of the unique identifiers in Worksheet1) However...I would like the macro to automatically extend the rows in worksheet2, to accommodate the ever increasing rows of worksheet1 Here is my specific code worksheet1 = "Invoice Record" worksheet2 = "General Report" Sub CommandButton1_Click() ' A...

Messages Forwarded as Attachments Appear Blank
Hello, I am running an Exchange 2003 Org, with mixed Outlook XP/2003 clients, all on the latest service packs. I have one client (our CEO) that when he receives forwarded email from outside the organization, the forwarded message shows up as an attachment, but when he opens the attachment, it is displayed as a blank email (no to, from, cc, subject, body, or anything). I have checked the AV logs on every "AV for Email" gateway from the source to the destination, and I don't see anything getting stripped from the message. I have also checked all the AV servers, and made sure...

atipical UDT structures VC6 passed from VBA
Hi folks, maybe someone can help me on this item. I'm trying to pass a more complex structure from VBA to VC6 that should look like this: VBA part: Type C_str vv() as Double yy() as Double t as double End Type Type B_str zz() as Long cc() as C_Str End Type Type A_str a as Long b as B_str End Type VC part: struct C_str{ SAFEARRAY* psaVV; SAFEARRAY* psaYY; double t; }; struct B_str{ SAFEARRAY* psaVal; C_Str* c; // ??? ==>this should be a vector of C_str }; struct A_str{ int a; B_str* b; }; Questions: 1. How can I define in structure B that c is a...

Office 2007 non-commercial use + Code 78F when updating
Hi All, Have a couple questions relating a Vista/Office 2007. We bought a sony vaio laptop that came pre-intalled with a trial license of office 2007. I un-installed Office completely and install a fresh copy of our partner license copy of office 2007, but when loading any office application now its shows 'Microsoft Outlook non-commercial use' and same for word,excel, etc. Anyone have any idea why this is? Second question is relating to windows updates in Vista; I get an error Code 78F when trying to install any update, and the soultions i try doesnt seem to resolve this issue....

Series overlap/gap width with VBA
Does anyone know how to set the series overlap and gap width using VBA? Have looked through all the regular reference material (including KB search) and can't find any way to refer to these properties programatically. Thanks! K Dales Hi K, The macro recorder is a good way to discover these properties. It produced this code for me. With ActiveChart.ChartGroups(1) .Overlap = 20 .GapWidth = 50 End With Cheers Andy K Dales wrote: > Does anyone know how to set the series overlap and gap > width using VBA? Have looked through all the regular > ...

OL 2003; Question about two default attachments in HTMLMessage Format
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C51E85.7F26CCA0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0010_01C51E85.7F26CCA0" ------=_NextPart_001_0010_01C51E85.7F26CCA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Outlook 2003, XPP SP2=20 Hello, When I send an e-mail message in HTML message format, the recipient = (even when I send to myself) also receives two default attachments, = image001.png and image002.png. The first image is a what I would guess a = Microsoft ...

Msgbox Code Syntax Error
I have a message box that I want to display in the OnEnter event of a List box simply to give instructions as to how the user is to enter the info. My code for the Message box isn't working and I'm getting a syntax error - I also got an error that the system is expecting an "=" in the code. I've researched it but can seem to see what I'm missing. I only want an OK button on it and then to return focus to to the control to which the code is attached. Here's the code: Private Sub lbDamagedParts_Enter() Me!Label23.Visible = True Msgbox("For th...

Compile Database Code Problem
I need to convert a database to ACCDE and need to compile my VBA, however when I select Compile under the Debug menu I get the following error, "Method or data member not found". The problem is that the Sub it is identifing is a part of my database and works well when I use the database. The code that failed was the following with ".Combo2" highlighted in the second line. Private Sub Combo2_AfterUpdate() TransferSection = Me.Combo2 MsgBox "Transfer Section is " & TransferSection RunCommand acCmdRecordsGoToNew RunCommand acCmdSelectRecord...

Question about MFC source code.
Hi, all, I find that there are some redefinitions in afxHH.h. Something looks like this: #define HID_FILE_MRU_FILE1 0x1E110 #define HID_FILE_MRU_FILE1 0x1E111 // aliases: MRU_2 - MRU_16 #define HID_FILE_MRU_FILE1 0x1E112 #define HID_FILE_MRU_FILE1 0x1E113 #define HID_FILE_MRU_FILE1 0x1E114 #define HID_FILE_MRU_FILE1 0x1E115 #define HID_FILE_MRU_FILE1 0x1E116 #define HID_FILE_MRU_FILE1 0x1E117 #define HID_FILE_MRU_FIL...

WMP 11 will not play wmv files attached in e-mails
I cannot play any video files that are attachments within e-mails. For a while I was getting an error code that the codec could not be found. Now it doesn't even get that far... WMP opens and hangs "connecting to media." It also does the same thing now when opening any wmv files that are in links? I don't recall having any of these problems with previous versions??? hhheeeeeellllllppppppp!!!! WMP having problems with attached video files is pretty common. It can usually be worked around by saving the attachment to a separate file first and opening that file...

Code versus Int
Hi, Web applications often use a parameter to select a specific record. Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to retriev a specific mail message. Question 1) Is an Int type not prefered? Question 2) What is a smart way to generate unique random numbers? Date + number??? 051320101 = 05-13-2010-1 Thanks! Arjen Arjen (boah123@hotmail.com) writes: > Web applications often use a parameter to select a specific record. > Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to > retriev a specific...

Zip Codes 5 digits only
Is there a way to change zip codes in a spreadsheet to only display 5 digits? Hi, Use custom cell format 00000. This will add leading zeros, if the cod is shorter than 5 digits. - Asse -- Message posted from http://www.ExcelForum.com =LEFT(zipcode,5) will strip off the extended codes. Jerry anonymous@discussions.microsoft.com wrote: > Is there a way to change zip codes in a spreadsheet to > only display 5 digits? ...

How to customize error bars by VB code?
Hello NG, from a Visual Basic (6) project, I need to customize the error bar of a chart, to get a different value for each data point. In Excel, this is done with a range reference: ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=Hoja1!R1C3:R2C3" but I need to do this by VB code, in order to control a OLE-Chart. Reference to the datasheet-range is not working: oGraph.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlCustom, Amount:=oGraph.Application.DataSheet.Range("A1:A3") Any ide...

Using VBA to Print PDF Files
Hi: My customer has PDF spec sheets for their inventory items. When a pick ticket is printed, they wish to print the PDF spec sheets for each item on the pick tickets. I am very comfortable with the report event structure and know exactly how to get to the PDF files using VBA, but I am having a problem getting the docs printed. I've taken two approaches: 1. Drop ACROPDF.dll onto a User Form in the VBA project. To do this, I created a procedure on the form as shown: Public Sub PrintPDF(FileName As String) AcroPDF1.LoadFile (FileName) AcroPDF1.printAll PDFPrinter.Hide End Sub T...

How to send attachment to a filtered email list of people
Surely there must be a way to add an attachment to an email template? Do you have to use the email template when sending direct email through CRM? Whenever I do an advanced find and want to do an email to the group, the only choice is email templates that don't seem to have any attachment options. Someone please tell me they have a workaround for this. Much appreciated!! There may be a way, using workflow Create a new, manually invoked workflow rule that generates direct email. Don't use the template option. Then you can select an attachment. (See the unsupported "slug...

code list of UTF-8
i searching for a list of the double character encoding spezial charcters in UTF-8 such as äüö... Can any one send me link where i can find a complete list? -- best regards Rolf Brockmann Rolf Brockmann wrote: > i searching for a list of the double character encoding spezial > charcters in UTF-8 such as äüö... > Can any one send me link where i can find a complete list? See http://www.faqs.org/rfcs/rfc2279.html. Cheers, -- http://www.joergjooss.de mailto:news-reply@joergjooss.de Rolf Brockmann wrote: > i searching for a list of the double ch...

Access 2003 VB Code Export Query Failing with Access 2007 user
I have a set of excel export queries I created in an access 2003 DB. Now, some of my users have upgraded to Access 2007 and are running in to a "Parameter" error when the execute the "canned" excel export queries I created for them. My only presumption is that it is due to the fact that I have hardcoded the export function, to go to an Office 2003 Excel file, and for whatever reason, Access 2007 cannot handle this. This make sense? And if so, any suggestions? I suppose if there was an easy way to test what version of office/excel they are running, I could change...