Posts by mhabib

Hi all,

I've written a UDF to sum up all the bold numbers in a 1-column range. The initial code worked fine, except that if the specified range was an entire column then the computation took some time to process all 65536 rows. I added the condition that only those cells should be processed whose row is less than or equal to the row of the last used cell. For this I added the variable LastRow. Although the formula for computing LastRow works correctly in a Sub procedure, the value returned within the Function, for reasons best known to Mr. Gates, is always 1.

Would appreciate any help with this. My code is reproduced below.

Thanks

m

Function SUMBOLD(rngSumRange As Range) As Variant
    'LastRow returns erroneous result in function.


    Dim rCell As Range
    Dim LastRow As Long
    
    LastRow = rngSumRange.SpecialCells(xlCellTypeLastCell).Row


    SUMBOLD = 0


    If rngSumRange.Columns.Count > 1 Then SUMBOLD = "NUM!": Exit Function


    For Each rCell In rngSumRange
        If rCell.Row > LastRow Then Exit Function
        If IsNumeric(rCell) And rCell.Font.Bold = True Then
            SUMBOLD = SUMBOLD + rCell
        End If
    Next


End Function

Sub Junk()
    'LastRow computs fine in this.




    Dim rngSumRange As Range
    Dim LastRow As Long
    
    Set rngSumRange = Application.InputBox("Select Range", , , , , , , 8)
    MsgBox rngSumRange.Worksheet.Name
    
    LastRow = rngSumRange.SpecialCells(xlCellTypeLastCell).Row
    MsgBox LastRow
End Sub

Re: F2 to reformat Cell

Or try this code. (Be sure to select the relevant cells first)

Sub MakeValue()
    Selection.ClearFormats
    Selection.Value = Selection.Value
End Sub

HTH

m

Re: Adding days to a date

Assuming your date is in A1, and your regional settings are set to m/d/y then this might work:

=TEXT(DATEVALUE(MID(A1,FIND("-",A1)+1,100))+3,"m/d/y")&"-"&TEXT(DATEVALUE(MID(A1,FIND("-",A1)+1,100))+7,"m/d/y")

HTH

m

Re: Forumla

The averages on sheet "Time Taken to Transmit" may not be representative. As pangolin has suggested, you should enter file size as numbers rather than text (e.g. 5 instead of 5k, 8000 instead of 8mb, etc.).

Once this has been done, you could see what kinds of results you get with the help of regression analysis. The results of the regression analysis may be used with the help of VLOOKUP or INDEX/MATCH in the sheet "Task 2".

Are you familiar with running Regressions and using VLOOKUP and INDEX/MATCH functions?

Regards,

m

Re: Linked Workbooks: Disable Update Link Message on Open

Thanks Maqbool,

Your code works.

Application.AskToUpdateLinks = False

There are some D Functions in my linked workbook, and these require source workbooks to be open, otherwise they return errors. Got around this by setting Calculation to manual in the linked book's open event.

Thanks again.

m

Re: Linked Workbooks: Disable Update Link Message on Open

Thanks Maqbool, but there are two problems

1. The Open event fires AFTER "Update Links" message appears.
2. The code generates the following error

Quote

Compiler Error:

Method or data member not found

The Text .UpdateLinks = is highlighted for debugging.

Regards,

m

Re: Linked Workbooks: Disable Update Link Message on Open

Thanks pangolin, but the STARTUP PROMPT option is not available in Excel 2k.

Hi.

Does anyone know how to supress the "Update Links" message and NOT UPDATE links each time a linked workbook is opened?

Thanks

m

Re: make different color in group

Welcome to the forum!

Try using conditional formatting.

Alternatively, you can perform the task manually - but very quickly.

1. Set Grouping Level to 1 (click grouping button 1).
2. Select the cells that are to receive the white background
3. Click Edit > Goto (or press F5)
4. Click the "Special" button in the Goto dialog
5. Select the option "visible cells only" and click OK
6. Apply the shading as required

Hope This Helps

m

Re: Automatically Display Formula Pallette

Quote from royUK

What do you mean by: formula pallette

It's the dialog box that you get when you type =function name and then press Ctrl A.

For example, if I type =SUMIF and press Ctrl A, I see a dialog that prompts me for different arguments of the SUMIF function.

Thanks.

m

Hi All,

I'm looking for code that I could assign to a button, whereby the formula pallette for a specific UDF (say LIFOCOST) would open automatically.

Thanks

m

Re: Open Columnar form with focus on record No. 5

Thanks for the help. Problem solved!

Sorry for delayed response.

m

Re: Multi-Table Delete Operation

Hey Maqbool,

Thanks for solving my problem. Sorry I couldn't respond earlier, as I didn't get a chance to test it out until today.

Regards,

m

Re: Find same parts of values in other workbook

A couple of points regarding your code:

1. It looks like you're trying to match the last 6 characters of your text in the other database. If the city (or some other common word) appears in the same cell, then chances are that you'll end up with a lot of "false positives".

2. In your For/Next loop, you're updating variable z twice (z=z+1 in addition to Next). Also, there's no need initialize the loop counter in case of For/Next.

I don't use "Find" much, so can't help you with that part of the code.

HTH

m

Re: Find same parts of values in other workbook

Wouldn't it be easier to match up data with the help of client codes? I notice the last column in both your tables contains numbers like 4118, 4034, 4104, etc. Why not use these codes for matching rather than trying to pattern-match (a more difficult and error-prone process)?

m

Re: Multi-Table Delete Operation

Sorry, I mistakenly used square braces, which is making my table names invisible. So here's the post once again - I would've preferred to edit the original post, but couldn't for the life of me locate the edit button.

I have a Products table and a Discontinued table. The primary key for both the tables is ProdCode. I'd like to delete all records from Products whose ProdCode exists in Discontinued.

Can anyone provide me with SQL statement to do this?

Thanks

m

Hi,

I'd like to open a columnar form (say "Employee") and automatically take the focus to record #5 (or any other specified record number). I'm using:

DoCmd.OpenForm ...

But I haven't found the right argument to achieve my objective.

Please help.

Thanks.

m

Hi,

I have a [Products] table and a [Discontinued] table. The primary key for both the tables is ProdCode. I'd like to delete all records from [Products] whose ProdCode exists in [Discontinued].

Can anyone provide me with SQL statement to do this?

Thanks

m

Re: Obtain Range Ref AND Sheet Ref thru Inputbox

Thanks Norie. It works.

m

Hi,

I'm using the InputBox method (Type:=8) to obtain a range reference. What I want is to be able to obtain the sheet reference as well. Unfortunately, this code does not return the sheet name of the selected range.

Sub InputBoxTest()
    Dim rng As Range
    Set rng = Application.InputBox(Prompt:="select range", Type:=8)
    MsgBox rng.Address
End Sub

Any help would be much appreciated.

Thanks.

m