Posts by JF

  • Clear Borders From All Cells

    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.

  • Clear Borders From All Cells

    Re: Border Class


    Quote from xlite

    JF,


    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?

    Code
    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.

  • Clear Borders From All Cells

    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:


    Code
    LR = ActiveSheet.UsedRange.Rows.Count


 to 


  LR= range("B65536").end (xlUp).row


    but that gave the same result.


    if I change it to:


    Code
    LR=6


    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:


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

  • Countif with 2 Spreadsheets

    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:


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


    [code]


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

  • Pass Value from User Form to Code

    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.

  • Pass Value from User Form to Code

    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?

  • Using a Message Box to pick someone

    Re: Using a Message Box to pick someone



    Andy,


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


    Code
    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

  • Using a Message Box to pick someone

    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....

  • Using a Message Box to pick someone

    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.

  • Using a Message Box to pick someone

    Re: Using a Message Box to pick someone



    Andy,


    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...

  • Using a Message Box to pick someone

    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.


  • Pass Value from User Form to Code

    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.

  • Pass Value from User Form to Code

    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?


  • Checking for an error

    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:

    Code
    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

  • Checking for an error

    I have the following piece of code:


    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:


    Code
    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)

  • Use a With vs For Next

    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).

  • Use a With vs For Next

    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.


    Code
    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"
    
    Else
  End If


    so something like


    Code
    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?

  • need a formula on page 2 to search data on page 1

    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).


  • dividing a .csv file into 2 smaller files

    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.


    Sincerely,
    greg


    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.

  • text with number into number

    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:


    Code
    =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....