Posts by jlsprink

Hi All,

I'm looking for code to hide all rows but the header row if the sum in two different columns both equal 0, otherwise no action is required. I've attached a spreadsheet as an example. I found similiar topics while searching but was unable to adapt anything for my needs.
Again, I need to hide all rows but the header, if both the amounts in column Z total 0 and the amounts in column AJ total 0.

Thanks for any assistance.

JL

Re: Delete Rows Based On Cell Content Ending In Alpha Characters

Hi mikeyc1204

Arghhh, Please accept my apoligies. I got confused with my own code and what I was trying to do. Your code did exactly as I requested and I thank you for that. My initial code actually deletes everything that doesn't begin with "9" or "ID9".

The new criteria should also KEEP any rows that have a cell value that ends with four alpha characters.

I'll have a go and see if I can modify the code you had provided.

Thanks a ton for your help!

JL

Hello,

I've got some code that will delete rows based on two criteria. It works well for my needs. Problem is now I have been given additional criteria and I haven't been able to adapt what I currently have.

My current code is :

Sub DeleteAllbut9()
     
    Dim iLastRow As Long
    Dim i As Long
     
    iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = iLastRow To 2 Step -1
        If Left(Cells(i, "C").Value, 1) <> "9" And Left(Cells(i, "C").Value, 3) <> "ID9" Then
            Rows(i).Delete
        End If
    Next i
          
End Sub

This code deletes all rows where the value in column C starts with a "9" or "ID9".

I now need to also delete the row if the value in column C ends with a string of 4 alpha characters.

Any help would be appreciated.

Thanks!

JL

Re: Extracting Time From Date & Time

Hello,

Can you use?:
=RIGHT(A1,8) format as general works for me. Assumes your data is in A1.

HTH

JL

Re: Delete Rows With Zero In Column

Hello,

Here's my nonelegant attempt:
See if this does what you need on a copy of your worksheet.

Sub DeleteRows()
    Dim lRow As Long
     
    For lRow = 65536 To 1 Step -1
        Select Case lRow
        Case 15, 20, 25, 48, 70, 104, 116, 155
            Rows(lRow).EntireRow.Hidden = True
        Case Else
        End Select
    Next lRow
    On Error Resume Next
    For r = Range("I65536").End(xlUp).Row To 1 Step -1
        If Cells(r, "I").Value = 0 Then
            Rows(r).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
    Next r
   Cells.Select
   Selection.EntireRow.Hidden = False
   Range("A1").Select
End Sub

HTH

JL

Re: Date

Hello,
Put:
=Today()-1 in desired cell.
HTH
JL

Re: Save As .txt Format Keeping Column Data Aligned

Thanks Bert01! That does indeed do the trick. Thank you very much for the assist.

Regards,

JL

Hi All,

I'm have a really hard time trying to save an excel file to a text file. When I attempt to do this, my header information does not line up with the corresponding information below. I have a macro that saves the excel file with the file format xlTextPrinter.

ActiveWorkbook.SaveAs "C:\Original.txt", _
    FileFormat:=xlTextPrinter

The result can be seen in the attached .txt file that I have attached.
While searching for a resolution, I saw a recommendation to use the Text to Columns feature in Excel, so I tried doing that for every column before saving as a .txt file. This looked a little better, but there were characters being dropped off of some entries. (I have pasted that attempt below the first attempt in the .txt document)
I'm having to save several .xls files to .txt in order to upload to another system that is used for archiving reports. If anyone has any advice, that would be great.
Thanks!

JL

Re: Determine Cell Value Based On Multiple Criteria In Two Columns

Hi AAE,

That is perfect! Thank you very much. I updated the formula because I did not want the calculation to take place if the Instrument Types were anything other than the six instrument types that I had listed. I was able to handle that though since you nicely broke out the formulas into separate cells in your attachment. Really appreciate you doing that for me, I learned a lot looking that over. It now looks like this:

=IF(AND(A9<>"ABS",A9<>"CMP",A9<>"CMV",A9<>"CRF",A9<>"CRP",A9<>"CRZ"),H9,IF(AND(OR(A9="ABS",A9="CMP",A9="CMV",A9="CRF",A9="CRP",A9="CRZ"),AND(OR(LEFT(B9,1)="A",LEFT(B9,3)="BBB",LEFT(B9,3)="Baa"),OR(NOT(ISERR(FIND("A",B9))),NOT(ISERR(FIND("BBB",B9))),NOT(ISERR(FIND("Baa",B9))))),AND(OR(LEFT(C9,1)="A",LEFT(C9,3)="BBB",LEFT(C9,3)="Baa"),OR(NOT(ISERR(FIND("A",C9))),NOT(ISERR(FIND("BBB",C9)))))),H9,0.15))

I've tested the formula on my real spreadsheet and it looks to be doing exactly what I want. Please be assured that your efforts and time on this is most appreciated. You saved me a ton of stress. Great job!

Regards,

JL

Re: Inserting

Hello,

Worked in my test.
Are you sure you have the sheet name correct? "tblOAG_AgentInfoFinale"
Spelling, no leading or trailing spaces, etc?

Regards,

JL

Re: Networkdays Calculation

Hello,

Try:

Sub CopyFunction()
Range("M20").Formula = "=NETWORKDAYS(K20,L20)"
     Range("M20").AutoFill Destination:=Range("M20:M" & Cells(Rows.Count, 12).End(xlUp).Row)
End Sub

hth

JL

Hi All,

I need some help in determining how to go about getting information populated in a cell. In one column I have various codes for Instrument Types. In the next two columns I have a S&P rating and a Moody's Rating. I'm trying to populate a percentage amount in another column based on what is reported on in the first three columns.

The Criteria I have been given in layman's terms is as such:
If column A is either "ABS", "CMP","CMV","CRF","CRP", or "CRZ",
AND the corresponding cell in Column B has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*" where the asterisk denotes wildcards,
AND the corresponding cell in Column C also has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*",

Then the value in Column R is good and should not be altered,
Otherwise the value in Column R should be .15 .
This is somewhat related to my previous post below, but I was given additional criteria to work with and I can't get my head around it. http://www.ozgrid.com/forum/showthread.php?t=93124

Any help would be very appreciated.
Thanks!

JL

FYI....Here is the code I've been playing around with for the past couple of hours. Looks logical to me, but what do I know. I must be doing something horribly wrong as it's not coming up with my expected results.

Sub MyTry()
    Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If c.Value = "ABS" Or c.Value = "CMP" Or c.Value = "CMV" Or c.Value = "CRF" Or c.Value = "CRP" Or c.Value = "CRZ" _
        And Left(c.Offset(0, 1), 1) = "A" Or Left(c.Offset(0, 1), 3) = "BBB" Or Left(c.Offset(0, 1), 3) = "Baa" _
          And Left(c.Offset(0, 2), 1) = "A" Or Left(c.Offset(0, 2), 3) = "BBB" Or Left(c.Offset(0, 2), 3) = "Baa" Then
'Do Nothing
Else:
       c.Offset(0, 7) = 0.15
        End If
    Next c
End Sub

Thanks again for taking the time to look at this.

JL

Re: Hiding Columns To Change View

Hello,

I played around with a userform example for a similar request by joseph_r03. Perhaps you can adapt if it fits your needs.

http://www.ozgrid.com/forum/showthread.php?t=82123

HTH,

JL

Re: Deleting Duplicate Rows Based On Ticket Number

Hello,

I agree with AAE and I'm sure that advanced filter would probably be the best answer, but I played around with it a bit and came up with possibly another solution. If you concatenate columns A and B and then run the attached macro that I modified from another post, It appears to give you the results you are looking for. (Just an attempt on my part)

Sub RemoveDupe()
    Dim rCell As Range
    Dim rRange As Range
    Dim lCount As Long
     
    Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
    lCount = rRange.Rows.Count
     
    For lCount = lCount To 1 Step -1
        With rRange.Cells(lCount, 1)
            If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
                .EntireRow.Delete
            End If
        End With
    Next lCount
End Sub

See attached and run the macro to see if it is what you are after.

Cheers,

JL

Re: Hide Rows Based On Cell Contents

Hello,

This seems to work:

Sub Hide()
    Dim Z As Long
    Dim x As Range
     
    Z = Range("A" & Rows.Count).End(xlUp).Row
    Set x = Range("A2:A" & Z)
     
    Application.ScreenUpdating = False
    For Each Cell In Range("A18:A" & Z)
        If Cell.Value = True Or Cell.Value = "T" Then
         Cell.EntireRow.Hidden = True
         End If
    Next Cell
     
End Sub

HTH

JL

Re: Keep Text In Cell

How about widening the column?
Double click on the border between the N and O to autofit.

JL

Re: Autofit All Columns In Many Worksheets To Cell Content

Hello,

Maybe,

Sub AutofitColumns()
    Dim wrksht As Worksheet
    For Each wrksht In Worksheets
       
    wrksht.Select
    Cells.EntireColumn.AutoFit
       
    Next wrksht
End Sub

HTH

JL

Re: Offset Cell Value Based On Criteria

Ahhhh, I understand that bit now. Thanks [email protected]!

Still strange that when I run the code below, it changes column G on a partial match. (i.e. any cell in column A containing the letter "B" or a "C" seems to get the .15 value.

Sub RatingsFix1SP()


    Dim FindWhat, rngCell As Range, i As Integer
     
    FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
    For i = 0 To 5
        For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = 0.15
                  End If
        Next rngCell
    Next i
     
End Sub

Again, msoliman's code worked for my purposes.

Thanks again for your help.

JL

Re: Offset Cell Value Based On Criteria

Hi [email protected], msoliman

Thanks much for your input.

Quote

Why is i set from 0 to 3 if it's to cycle through the array which holds 6 elements?

That's just me not adapting the code I found correctly.
However, when I changed the line to read from 0 to 6, I got a "subscript out of range" error for the following line:

If InStr(rngCell, FindWhat(i)) <> 0 Then

No worries though as msoliman's code seemed to do the trick.
Thanks a bunch msoliman, really appreciate your help!

Regards,

JL

Hello,

I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G)
I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.

Sub RatingsFix1SP()
     
    Dim FindWhat, rngCell As Range, i As Integer
     
    FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
    For i = 0 To 3
        For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 6) = 0.15
                  End If
        Next rngCell
    Next i
     
End Sub

To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%.

It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.

If someone can tell me what I've done wrong or has a better solution, it would certainly be very much appreciated.

Thanks kindly,
JL