Posts by Joe Derr

Joe Derr · Feb 16th 2011

Re: Trim last name

snb,

Thank You!

Man I love this forum.. thanks everyone that has helped me in the years past..

snb, thanks for the quick reply, that did it..

Joe

Joe Derr · Feb 16th 2011

Hey all,

This code below works as advertised, but when there is only the last name only already in the cell, it basically trims that so it ends up a blank cell.

Code

strName = Trim(Cell)
        lSpace = InStr(1, strName, " ", vbTextCompare)
        Cell = Trim(Left(strName, lSpace))
    Next

So the cell contains: (just the last name)

DERR

it will trim it to a blank cell

If the cell contains: (last and first name)

DERR JOE

it will be trimmed to:

DERR

Which is correct..

Is there a modifcation of the code to detect nothing to the right of the first space and then leave it as is.. if there is anything to the right of the first space, then it trims it out?

Thanks in advance!

Joe

Joe Derr · Feb 16th 2011

Re: Extract numbers after dash

Hey Stephen,

Meant to say thanks again for that last bit of code.. it works perfectly!

Joe Derr · Feb 8th 2011

Re: Extract numbers after dash

One Last Thing.. as I was running the code (awesome code) I noticed an issue..

Is there a way to determine a new month and start over from 01 rather than continuing to count up?

20110131-156 31 Jan 11
20110201-01 01 Feb 11

I just thought of it as I ran the last data from the 16th to, today.. I dont even know how to T/S your line of code or I would take a whack at it...

Sorry to be a pain, your code is awesome!

Joe

Joe Derr · Feb 8th 2011

Re: Extract numbers after dash

StephenR,

Awesome!! That did it.. I would have never be able to come up with that myself, thank you. I have put commented in a credit to you in the file for your fix to this problem.

Thanks again!!

Joe

Joe Derr · Feb 8th 2011

Re: Extract numbers after dash

StephenR,

Thanks for that code... it does the last digits as advertised, but the date in Column B has to factor into the first part of the number..

Example---------------------------------------------------------
Column A in the 1st row is 20110203-123 Column B is 3 Feb 2011
Column A is the 2nd row is blank, Column B has the date 5 Feb 2011

I am looking to read in the date from column B, convert it from MMDDYYYY to YYYYMMDD

then combining the last digits past the dash from the row above... so it would be like..

20110205-124

If it's possible.. thanks for the help....

Joe Derr · Feb 8th 2011

I have tried multiple right, left and trim functions, but everything I found is hard coded to last two or last 7 digits.

I have a date formated tracking number and I want to increment it by one, however the date might be different from one line to the next, but not always.

eg.
Col A Col B

20110201-15 01 Feb 2011
20110201-16 01 Feb 2011
20110202-17 02 Feb 2011
20110203-18 03 Feb 2011
20110203-19 03 Feb 2011
20110203-20 03 Feb 2011
04 Feb 2011
04 Feb 2011
04 Feb 2011
05 Feb 2011

What I am looking to do, is increase the number, by extracting the number after the dash, and adding it to the converted date, then writing it to column A until there isn't a date in Column B, indicating the last line of Data. I guess a Do until Nothing? or ??? The next 4 will be

20110204-21
20110204-22
20110204-23
20110205-24

See the attached test book.. It doesn't work, I can get the date read and converted, but I can't get just the last digits.. they will vary from two to three digits. Like I said all the examples hard coded 2 or 7 digits..

So some of the numbers in Column A could be like

20110205-123
20110206-124

etc..

Thanks for any guidance..

Joe Derr · Feb 7th 2011

Re: compare sheets delete matchs

Windy,

Worked perfectly! Thank you for all your hard work on this!

Thanks again!!

Joe

Joe Derr · Feb 4th 2011

Re: compare sheets delete matchs

Ran into a problem implementing the code.

On my sheet 2, I have data from A to I with Columns A and H being blank (ready to accept other code down the line)

When ever I run the code, it errors as soon as I put even one line of data on Sheet 2... I see this part of the code from above

Code

wsSheet2.Range("B1:C" & lngRows2).AutoFilter Field:=2, Criteria1:=1

Is there a way to use another column for temp info.. like Column A or H or even anything past Column J ?

Joe Derr · Feb 3rd 2011

Re: compare sheets delete matchs

Brilliant !!
Thank you for the help, that is EXACTLY what I was wanting it to do, and even did the copy bit too. You are outstanding! Its funny that I can search myself and not come up with anything close, but others can. Even the suggested topics didn't pull that out. Thank you again, I will make reference in the code to both you and Batman (the other link) for the coding.

Joe

Joe Derr · Feb 3rd 2011

Re: compare sheets delete matchs

Windy,

Thanks for your reply.

The next step would be to copy the remaining rows of data to the next open column in Sheet1. I didn't include that, because this part was already daunting enough, seamingly.

The file is of course what I have, and it's broken. I included more detailed narrative on the sheets, and when you launch the user form, and press the button, nothing seems to happens, but what is going on, is there is a continous loop... it never ends, so you have to hit CTRL and Pause/Break to break out of the loop, then you goto the code hover over the variable "iCtr" and you will see you stopped at some number along the way.. it just doesn't work..

Thanks for looking.

Joe Derr · Feb 3rd 2011

Hi All,

I have looked and found tidbits of code but can't seem to get anything to work.

I have two sheets, for simplicity they are Sheet1 and Sheet2
In Column B I have numbers, not sequencial, but sorted from lowest to highest.

Sheet two is populated via a database that exports to excel
Column B is the numbers again, but there might be more of them.. as the database is used, the numbers increase..

From Say.. Jan 1 - Jan 26 there could be 50 numbers, on the 27th 5 more entries, then nothing until the 30th when 10 more are put in.. from the last export, on the 26 where there were 50 entries, now on the 30th when I run the "report" if you will, there are 15 more. However the report will pull all 65 reports.

What I am trying to figure out how to do, is loop through Sheet2, read in the first number in Column B, then look in Column B of Sheet 1 to see if it's already on the sheet, that is the tracking sheet. If it matches, then I would like it to delete the row entirely on Sheet 2 (the imported sheet from the database) then loop to the next number, on Sheet2 Column B, look at sheet 1, Column B and see if that one is there.. yes... delete that row from Sheet2 etc until the end.

I have tried to combine find, with delete and just about deleted my whole project

I need it in excel VBA, I found a vlookup but it was sheet based.

Thanks in advance.

Joe

Joe Derr · Oct 28th 2010

Re: Last Sheet Name Closed Workbook

Cytop, AAE, and Clarity,

Thanks for your help, Cytop's code fit perfectly. I appreciate everyone's help, I wasn't sure if this was ever going to work right.. AAE, I kept seeing how to find the last worksheet of the workbook I am in, but never anything from another workbook, just what tabs are in there at best... Again Thanks for all your help, Clarity too.. I bookmarked that page for future reference, thanks for pointing it out.

Cheers!

Joe

Joe Derr · Oct 27th 2010

Re: Last Sheet Name Closed Workbook

Clarity,

Thanks.. I will look over those.. However, as my search has led me in circles... I keep finding bit's and pieces that start to work, but then error.. ADO, checking to see *if* there is a certain sheet, but nothing that will say, ok Joe.. you browsed to this file, good.. here is a list of Sheets and this is the last sheet... storing it as a variable... as such, I need to use it in this part of the code..

Code

'Code found at:
'http://www.exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html


' In case user cancels the browse for file, it won't error out
On Error Resume Next
 
Dim wb As Workbook, fn As String
 
' Browse to the file to import
fn = Application.GetOpenFilename
    
 ' turn off the screen updating
    Application.ScreenUpdating = False
 
    Set wb = Workbooks.Open(fn, True, True)


' open the source workbook, read only
    With ThisWorkbook.Worksheets("Sheet1")


' read data from the source workbook
        .Range("A5:Q12").Formula = wb.Worksheets("OCT 26").Range("A54:Q61").Formula
    End With


' close the source workbook without saving any changes
    wb.Close False
    Set wb = Nothing ' free memory
 
 ' turn on the screen updating
    Application.ScreenUpdating = True

Display More

Specifically this part:

Code

' read data from the source workbook
        .Range("A5:Q12").Formula = wb.Worksheets("OCT 26").Range("A54:Q61").Formula
    End With

The hard coded OCT 26 needs to be whatever the last tab is in the workbook that I select.. time marches on..

If anyone has found anything that works.. then please let me know.. I will post if I ever figure it out.. there has to be something somewhere... I just cant find it...

Joe Derr · Oct 27th 2010

All,

I have tried a billion ways (it seems anyway) to browse to a workbook, and then extract data from it, but to do that, I need to know what the last worksheet is.. it is variable (Daily) product that is created by another department and sent to us for analysis. What I am trying to accomplish, is it has tons of extra data and now, I delete the rows of un needed data and manually manipulate the data.. What I want in the end, is press a button on my file, I will browse to the saved .xls file, and the program will look into that worksheet, find the last sheet, then extract data from a range and import that to my worksheet for me.

What I need to know what is the code that will look into the workbook I select and find the last tab, again it changes on a daily basis.. They create the excel file, so I can't load any code on that side to help. Is this possible?

Here is the code I have thus far to open browse and give me the path, and file name that I selected, I am sure I will need that along the way.. Thanks for your help!

Code

Private Sub CommandButton1_Click()
' I have this call like this because I keep changing it because of the multitude of ways I kept trying
' to get this to work right..
 
  TransferData
End Sub
 
Public Sub TransferData()
 
    Dim FilePath As String, FileName As String, fn As String
    
    
'Give Open dialog to browse for file to import
    fn = Application.GetOpenFilename
    
'Hacks up the Path and File name
    FilePath = gsGetPath(fn) 
    FileName = FunctionGetFileName(fn)
    


' To stop the sub until I can figure out the last sheet problem I am having
Exit Sub


... more coding here after I can figure out the last tab thing...


End Sub

Display More

Joe Derr · Oct 13th 2010

Re: Target Row Height

AAE,

You are right, I did create a new test workbook to test your code, independently of my workbook and, as you said.. it did work. I will see if I can find anything that is keeping it from working correctly. Thanks for your post.

UPDATE: I realized I didn't set my rows to 21.75 and when I ran the test, it reduced it back to 15.00.

Scratching head.. any ideas?

Joe Derr · Oct 12th 2010

Re: Target Row Height

Thanks for the reply, I can't seem to get it to work, after I delete the content out of the row..

IE. It expands to except the long text but when I delete the text, it just returns the line to 11.25. I would like it to be 21.75 if possible.

This is just to return the lines to the normal height of 21.75 if there was an erronous input.

Is there a way to detect the delete key in the target area?

Joe Derr · Oct 12th 2010

Hey all,

Trying to get the code below to work. I have it set to expand when long text is inputted (it is set to word wrap) however when I delete the text, I would like it to set the row height to 21.75.

This code is inputted into the sheet object, so it's really not a function.

Code

' ===============================================================
' This function automatically resizes the Descrepancy column H
' to expand to fit the whole write up.
' ===============================================================
    
    If Target.Column = 8 Then
     
     Target.EntireRow.AutoFit
    
    End If

I tried this but it still reduces the row to 11.25.

Code

' ===============================================================
' This function automatically resizes the Descrepancy column H
' to expand to fit the whole write up.
' ===============================================================
    
    If Target.Column = 8 And Target > "" Then
     
     Target.EntireRow.AutoFit
     
    Else
    
     Target.Height = "21.75"
    
    End If

Display More

I haven't alot of experience using Target, so any help would be appreciated.

Joe Derr · Sep 8th 2010

I have a formula that I am using to try to clear another cell, based off of a data validation list drop down, this is all "on sheet"...

Column P - is a time input
Column R -
Column S

Joe Derr · Aug 10th 2010

Re: Insert row with formulas conditional formating and data validation

SMC,

I tried your code, but it still did it, looked like it was going to insert a line but still only ended up with 3 lines.

Maybe I need a way to:
* Insert a line below.. I have done this already..
* Then it will need to copy the formulas, data validation, and conditional formatting that is unique to each row via VBA maybe???
* Re-align the rows so they are sequencial down to 150 for each column.

These lines can be inserted anywhere from Row 10 to 150, there are prefilled boardered lines in that whole range.

I will see if I can break down the workbook so I can send in an example..