Posts by JF

    Re: Border Class

    Quote from Seti

    Do you have any merged cells? Any protected cells?

    Nope, I have code to unmerge and unprotect all cells prior to getting to that point.

    Re: Border Class

    Quote from xlite


    i used both instances of LR and both works ok.

    i don't know what caused the error, but can you try on yr side if the select method works prior to bordering the range?

    LR = Range("B65536").End(xlUp).Row
    Range("B2:B" & LR).Select

    I had already thought of that. The range select did work but for some reason the formating would not. I also noted that if I tried to do it manually, I could not as well (Unless I went past the area I actually wanted). Although I would highlight the range, when I tried to use Format Cells, the dialog bog would not come up.

    I have the below portion of code that is all of a suddent causing an error.
    The report that we use with this code will be changing slightly when we upgrade the program that generates the report. The report still is output as an excel file but some of the columns are in different locations. So I had to change the lines of code to the appropriate columns. After doing so this portion of the code now errors out:

    I get a "Unable to set the LineStyle property of the Border Class" error (Run Time Error 1004) when it attempts to set a border on the right edge of the range.

    In my sample Data, LR calculates to a 6 as this is the last row of data. I tried to change as follows:

    LR = ActiveSheet.UsedRange.Rows.Count
      LR= range("B65536").end (xlUp).row

    but that gave the same result.

    if I change it to:


    then the code runs.... I can not figure out why. I never had this problem previously. Can anyone help with this?

    Also just found out if I modify as follows it runs:

    With Range("B2:B" & LR + 1)

    Re: Countif with 2 Spreadsheets

    Quote from cbanks

    yes, that would be great. but i am needing this in vba. I am great with functions but vba is what i am needing. thanks for your help

    I used a range of a1:b24 for the test data on my "Data Sheet" tab and a1:a6 on "Sheet2" tab. I used the following code:

    With Range("c1:c6")
    .FormulaR1C1 = "=SUMPRODUCT(('Data Sheet'!R1C1:R24C1=RC1)*('Data Sheet'!R1C2:R24C2=1))"
    .PasteSpecial Paste:=xlPasteValues
    End With


    it put the sumproduct formula in c1-c6, then copyied and did a paste special/values to that range. See the attached

    Re: Pass Value from User Form to Code

    Thanks, Yes I did originally have the public variable in the this workbook area. Not a big deal for me, I can use it in either area. Thanks for the info.

    Re: Pass Value from User Form to Code

    Quick Question....

    When I used the code in the "This workbook" section, it did not work. However when I put it in a standarrd module it did.... Was wondering why it does not work from the "this workbook" area?

    Re: Using a Message Box to pick someone


    Just noticed a problem with your's.. it errors after the third time... I modified it as follows and it seemed to correct it.

    Sub Buton1()
        Static ClickNumber As Long
        If ClickNumber > 3 Then ClickNumber = 1
        If ClickNumber = 0 Then ClickNumber = 1
        MsgBox Choose(ClickNumber, "Tom", "Sally", "Jane")
        ClickNumber = ClickNumber + 1
    End Sub

    Re: Using a Message Box to pick someone

    I like it... your's is shorter and doesn't require the use of a public variable as well....

    Re: Using a Message Box to pick someone

    Quote from royUK

    have you tested your code?

    I ran it and it seemed to work. First Click showed Tom, next one showed "Sally" and the third click showed "Jane". On the 4th click, it started over again.

    Re: Using a Message Box to pick someone


    Won't this solution just show three messages (one after the other) with just one click of the button? Unless I read his post wrong, he wanted the name to change with each succesive click on the button itself...

    Re: Using a Message Box to pick someone

    Quote from stevehorton09

    Good weekend to you all,

    Basically I want a button that once pressed generate a message box that says a persons name. There are 3 names, so the message box should show name 1 1st and then when pressed again show name 2 and you guessed it then name 3!

    If anyone could let me know how to do this or have teh code that would be great

    After you add your button to the sheet, create the below macro and point the button to it.

    Re: Pass Value from User Form to Code

    Thanks Andy,

    I was able to get it with the public variable method. I had figured it out last night about 40 minutes after I posted. But good to see the other methods as well.

    I have created a custome User Form with three option buttons inside a frame.

    How do I set the value for each button and then how do I pass that value back to the code that opened the form (after closing the form upon a selection?

    what I have so far is:

    in the form I have the following code associated with each of the butons (but I do not think I have it right). Isn't there a way (becasue I have them in a frame) to shorten the code?

    Re: Checking for an error

    Quote from thomach

    If I remember correctly, if there are no zeros present, then your FIND will set SR1 = 0. You might want to verify this. If correct, then you can use:

    On Error Resume Next
    SR1 = Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Row 
    On Error GoTo 0 
    If SR1 <> 0 then Rows(SR1 & ":" & tr1).Delete Shift:=xlUp

    Thanks TOMACH, this worked perfectly

    I have the following piece of code:

    SR1 = Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Row
      Rows(SR1 & ":" & tr1).Delete Shift:=xlUp

    what I need to know is how to trap the in the event it does not find any (in my case the column will only contain 1's or 0's. If there are no 0's then it should skip the line:

    Rows(SR1 & ":" & tr1).Delete Shift:=xlUp

    and continue with the line after that (which happens to be return to the macro that start the marcor this code is in)

    Re: Use a With vs For Next

    Quote from norie

    As far as I can see you won't be able to use that method.

    Why not use an empty column to put a formula in and the copy and paste special to F as values?

    Thanks Norie, I thought of that but I am working with a small amount of data so it does not take long for the loop to run. I was just trying to see if there was a more efficient way of doing it (for my own learning purposes).

    I have the following bit of code. While this code does what it is supposed to, is it possible to use the With command vs using the for/next loop.

    For x = 2 To Range("F65536").End(xlUp).Row
      'MsgBox ("left of cell is " & Left(Range("F" & x), 4))
      If Left(Range("F" & x), 4) = "PERM" Then
        Range("F" & x).Value = "Hard Bounce"
        ElseIf Left(Range("F" & x), 4) = "TEMP" Then
        Range("F" & x).Value = "Soft Bounce"
      End If

    so something like

    lr = range("F65536").end(xlUp).row
    with range(cells(2,6), cells(lr,6)
       if left(    ,4)="PERM"......
      Elseif left(    ,4)="TEMP".....
    End With

    I'm not sure how to referance the cells for the with method. Can it be done or is the for/next loop the best way?

    Re: need a formula on page 2 to search data on page 1

    You could try something like this: In my test, I had the sample data in rows 1-8 (row 1 as header).

    Re: dividing a .csv file into 2 smaller files

    Quote from need_a_name

    Hi Rich,

    Tried that and it still takes 40 min to get thry 60000 rows of data.


    Here is code that I use to import large .txt files into excell... it automatically parses at 65536 rows. I have done in excess of 100,000 rows in less than 1 minute with it.

    I found this code here on Ozgrid quite some time ago.

    Re: text with number into number

    This would work with one exception, the result could appear as a number but actually be seen by excel as text. to get around this, try the following:

    =left(a1,find(" ",A1)-1)+0

    this will pull all data to the left of the space (in case you have more than 6 digits prior to the space "Pts". It will then convert to an actual number format so you can do calculations if needed.

    BTW, this is a tip from Dave Hawley's book Excell Hacks - Thanks Dave....