Posts by ChrisBEE1

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Find method: using one range to search for all occurences in another

    Hello All:


    I trying to use the find method with two ranges. My objective is to locate all occurences of the values of range 1 in range 2. My macro finds all the values, however, it loops to many times. Here is a copy of my code. Any help with this problem will be greatly appreciated.


    TIA,
    Chris


  • VBA : how to do a findnext on un-active sheet

    Hi All:


    VBA is great but it sure can be perplexing.
    I am working on a project and I need some VBA-code to perform a "find" then a "findnext" using a userform. Does anyone have something along these lines that I can take a gander at see where I'm going wrong?


    In the code that I have used the variable that should receive the value to implement the "findnext" is not being detected.


    Any help I can get on this is greatly appreciated.


    code example:
    -------------------
    Private Sub FindIt_Click()
    ActiveSheet.Protect UserInterfaceOnly:=True, password:="engineer"
    Dim foundCell As Range
    Dim foundValue As Range
    Dim searchrng As Range
    Dim textToBeFound As String


    textToBeFound = TextBox5.Value
    Set searchrng = Sheets("Codes").Range("B773:B2638")
    'Find SIC Description and code along with NAICS description and code from input in "Find It" box


    With searchrng
    Set foundValue = .Cells.Find(What:=textToBeFound, _
    after:=(.Cells(.Cells.Count)), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    FindIt.Caption = "Next>>"
    CommandButton2.Caption = "<<Previous"


    Set foundCell = foundValue
    Set foundValue = searchrng.FindNext(after:=foundCell)


    'test to see if any matching text was found using an IS test

    If foundValue Is Nothing Then
    MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
    Exit Sub
    End If
    End With
    TextBox8.Value = foundValue 'loads SIC Description
    TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code
    ActiveSheet.Range("f14").Value = foundValue.Offset(0, -1) ' loads search variable for "Next" and "Previous" arguments
    TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description
    TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code


    End Sub


    TIA,
    Chris:(:(:(

  • VBA : Userform Questions

    Thanks Guys:


    Thanks you so much for your assistance, I really appreciate your help. Things are finally beginning to look up.


    I have implemented some of the code that you suggested and I am now able to initiate a search, retrieve both 2 descriptions and 2 codes to the userform simultaneously, and assign the found code values to their intended destination cells on the spreadsheet. But I have yet to get the application to perform a "next" or "previous" search.


    In my application the userform queries the end-user for input, performs a search which is designed to retrieved 2 corresponding codes along with their descriptions. This allows the user determine if he has obtained the appropriate code.


    Here is a copy of the code:
    -------------------------------------
    Private Sub FindIt_Click()
    Dim foundCell As Range
    Dim foundValue As Range


    Set foundCell = foundValue
    'Find SIC Description and code along with NAICS description and code from input in "Find It" box
    200 Set foundValue = Sheets("Codes").Range("b773:b2638").Find(What:=TextBox5.Value, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    FindIt.Caption = "Next&gt;&gt;"

    'test to see if any matching text was found using an IS test
    If foundValue Is Nothing Then
    MsgBox "No Match Found! Either refine your description and try again, or consult the Source Industrial Code (SIC) table for the appropriate description."
    Exit Sub
    Else 'If text found, go to cell where text found



    TextBox8.Value = foundValue 'loads SIC Description


    TextBox6.Value = foundValue.Offset(0, -1) 'loads SIC Code


    TextBox9.Value = foundValue.Offset(0, 2) 'loads NAICS Description


    TextBox7.Value = foundValue.Offset(0, 1) 'loads NAICS Code

    End If


    End Sub
    ---------------------------------------
    How do I format the "After" argument being that my values are being sent to the userform?

  • VBA : Userform Questions

    Hi:


    I am in the process of developing a custom userform. I want it to appear very much like the standard "find" userform provided in Excel, however, than are a few exceptions. First, I want this userform to be capable of both next and previous searchdirections. Then, I want it to report the result of the find on the userform. Thus, far I my search starts fine but it will not report the value to the list box. Should I being using a listbox or a textbox?


    Anyway, here is a copy of the code:


    Sub listbox1_Click ()
    Dim foundvalue as Variant
    listbox1.value = foundvalue
    End Sub


    Is this code sufficient? HELP!!!


    TIA,
    Chris:(

  • How do you create a custom desktop icon?

    :question:


    Hi All,


    I have a custom application and I would like to create a custom desktop icon, as well as, replace the "Excel" icon to the left of the Microsoft Excel window caption. Is this possible and does anyone know how to accomplish this? If so, can you offer a code example?


    Thanks,
    Chris

  • Help!!! Would like to get the maximum value of each range a

    Hi All:


    I need help writing a macro. Here's my problem, I would like to get the maximum value of each range a column. For instance, I have two columns of data, column A contains sensor numbers, while column B contains sensor temperature readings, like so


    column A............. column B
    sensor #............sensor temp. (F)
    N2 ...................... 100
    N2 ...................... 115
    N2..........................90
    S3........................120
    S3........................115
    W4........................119
    E1........................100
    E1........................111
    E1........................112


    ......and so.


    I want a macro that takes into consideration that sensor numbers N2, S3, W4, and E1 entered in column A are all separate and distinct ranges. Once the ranges are is established I would like the macro to determine the maximum value of each range, report that value in a message box and change the background color of the cell.


    Here is the code that I have come up with:
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    Sub Get_Max_Value_of_Ranges()
    Dim rnga, rngb, Max, Rng, As Range
    Dim i as Integer


    Set rnga = ActiveSheet.Range("A7:A100")
    Set rngb = ActiveSheet.Range("B7:B100")


    For i = 7 to 100


    Set Rng = Intersection(rnga, rngf)


    Set MaxRng = Rng.Cells(Application.Match(Application.Max(Rng), Rng, 0)
    Msgbox "Max. Value for" & rnga & "=" & MaxRng


    MaxRng.Interior.ColorIndex = 3


    Set rngf = rngf.Resize(rngf.rows.Count + 1, rngf.Columns.Count)


    Next i


    End Sub
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;


    Thanks in advance for any help.


    Chris:rolleyes:

  • Help!!!Please with addr=Target.Address

    Hi ALL,


    I posed this question yesterday and thought that it was resolved, however, the solution opened the door to new problems.


    My project incorporates about a half dozen or more columns (each column with at least 240 rows) which run a macro using the find method when the user double-clicks a cell.


    Currently, I'm running this code:


    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    IPrivate Sub Worksheet_BeforeDoubleClick( _
    ByVal target As Excel.Range, _
    Cancel As Boolean)
    Dim addr As String
    addr = target.Address
    If addr = "$A$13" Then
    End If
    If addr = "$A$14" Then
    End If
    If addr = "$A$15" Then
    End If
    and so on................
    End Sub
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    and it works spendidly. The problem with this code is that it includes a reference for each cell where I want this "Double-Click" event to occur, thus it uses too much memory.


    I recently experimented with variations of this code that would be more efficient and reduced the amount of memory required to run the procedure. The code included the following:


    code:
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;CaIPrivate Sub Worksheet_BeforeDoubleClick( _
    ByVal target As Excel.Range, _
    Cancel As Boolean)
    Dim addr As String
    addr = target.Address
    If addr >= "$A$13" and addr <= "$A$100" Then
    Call 'FindRoutine'
    End If
    End Sub
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    The above code produces inconsistent results, sometimes it works and other times not at all.


    I also tried the following and at first I thought I had my problem resolved, but then I notice that while my routine would work in column "A", it would work before cell "$A$13" even though my control statement clearly defines the range where it should be activate.


    code:
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    Private Sub Worksheet_BeforeDoubleClick( _
    ByVal target As Excel.Range, _
    Cancel As Boolean)
    Dim addr As String
    addr = target.Address
    Select Case addr
    case "$A$13" To "$A$100"
    Call 'FindRoutine'
    End Select End Sub
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;


    Furthermore, I have tried to define my range with:
    Case Is >="$A$13"
    again the results are inconsistent.


    Any help onthis will be greatly appreciated.


    Chris

  • Help!!! Project returning "Out Memory"

    Hi all,


    My project has ballooned and it is now returning "Out of Memory" errors. I have deleted all unnecessary code and worksheets. Can anyone give me any suggestions of other things to try.


    My biggest memory hogs are the half dozen or more columns (each column with at least 240 rows) which run a macro using the find method when the user double-clicks a cell.


    Currently, I running this code:


    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    IPrivate Sub Worksheet_BeforeDoubleClick( _
    ByVal target As Excel.Range, _
    Cancel As Boolean)
    Dim addr As String
    addr = target.Address
    If addr = "$A$1" Then
    End If
    If addr = "$A$2" Then
    End If
    If addr = "$A$3" Then
    End If
    and so on................
    End Sub
    &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
    and it works spendidly. My problems is that my code includes a reference for each cell where I want this "Double-Click" event to occur, thus I have created a memory hog.


    Can this routine be altered to give the same result with less code?


    Can looping be incorporated with this code?

    Can any give an example of some code
    that acheives the desire goal by looping through these statements?


    Help Please!


    Chris:puzzled:

  • Please help with application.max

    Hi Denis,


    The code I dsplayed works fine. I need help modifying it to yield the following results: agents 1 thru agents 100 are all in column 9, the date of sale is in column 10, and the amount sold is in column 12. I want to highlight all the maximum sales values for each agent. Whereas, the code that I displayed is highlighting one singular maximum value.


    Looping will be required, but how do I start?


    Again, any help at all will be greatly appreciated.


    Thanks,
    Chris

  • Please help with application.max

    Hi bt,


    No I have not, according to Dave Hawley in "MS Excel Visual Basic Examples" that syntax can return errors. He suggest dropping the 'Worksheets'.


    Actually the code that I displayed works fines, I simply need it to do more. In it's present state it highlights "the maximum value" in the column. I need it to hightlight each maximum sale value for each Agent on a monthly basis.


    Thanks,
    Chris:puzzled:

  • Please help with application.max

    :puzzled:


    Hi all,


    Can someone help me with the vba code to find the maximum value in a column. My spreadsheet incorporates several columns Agent Number, Date, and Sales are the main concerns. I want an automatic procedure where my macro hightlights the maximum sales of each agent. How can adapt the following macro to suit my needs.


    ******************************
    code:


    Sub Find_Max()
    Dim searchRange As Range
    Dim cell As Range
    Dim maxValue, maxRow As Integer
    Set searchRange = Columns("L")
    maxValue = Application.Max(searchRange)
    maxRow = Application.Match(maxValue, searchRange, 0)
    Set cell = Cells(maxRow,12)
    cell.Select
    End Sub
    *********************************


    Any help on this would greatly appreciated.


    Thanks,
    Chris

  • Help, in a Protection Quandary

    Hi All,


    I am in a quandary over how to implement cell protection. It is common knowledge that for protection to work the cells must be locked and the worksheet protected. Well, when I try to implement cell protection using the following code:


    Sub Worksheet_Activate
    ActiveSheet.Protect UserInterfaceOnly:=True, password:="password"
    End Sub


    Either the target cells remain locked and a "Run-time error 1004' occurs and the macro stops, or the macro executes and leaves the target cells unlocked (and I'd like them to be locked when the macro has executed).


    Can anyone shed light on this quandary? How can I acheive my objective of protecting the cell contents? It has been suggested that I set the sheets scroll area, however, this solution does not work for my situation.


    Also, is there a way to lock cell after it has been unprotected?


    Any help that I could get on this will be greatly appreciated!


    Thanks,
    Chris

  • Almost done!!!

    Marcelo,


    This protection problem is exasperating.
    I have encountered the same protection problem you have described. I incorporated this statement in the worksheet module:

    ActiveSheet.Protect UserInterfaceOnly:=True, Password:="password"


    and I still receive the error message "Run-time error '1004': the cell or chart you are trying to change is protect and therefore read-only", each time I run my macros.

    You indicated that you had placed your protection statement in the wrong place. Where??? I trying to determine if I have made a similar error.


    Also, did you wind up using the suggestion supplied by Dave Hawley? If so did it work for you?


    Thanks,
    Chris

  • Printout VBA code

    Hi all,


    I trying to develop a macro to print my worksheets. Here's the problem, I never know how much data my end-users will incorporate into any one worksheet. In some instances, one worksheet can result in five pages or more of printout.


    How do I acheive the following:


    1.) set a page break so that if, for example, I have 400 rows or 4000 rows of data that each page print 40 rows data.


    2.) print multiple row column headings on each page.


    Iwould appreciate any help on the subject.


    Thanks,
    Chris:)

  • How can I control character display?

    Thanks bnix,


    I quess I was not going far enough into the object model. As it turns out there are only two arguments associated with the object 'character', start and length, respectively. To acheive my desired results, I managed to workaround this issue using an alpha character combined with the 'character' object.


    Thanks Again,
    Chris

  • How can I control character display?

    Thanks bnix,


    I tried the object browser first. It does not reveal the arguments associated with Characters, such as, Characters(arg1:=argument name, arg2:=argument name, and so on). That's what I'm looking
    for.


    Anyway, I appreciate your response, thanks for the info. I will try MSDN maybe I'll find it there.


    Thanks,
    Chris