Posts by bfft

Re: Trying to delete rows based on cell content

nailed it. Thanks

Re: Trying to delete rows based on cell content

I likely made a mistake posting this so late on Friday.....

Re: Trying to delete rows based on cell content

forum.ozgrid.com/index.php?attachment/72111/

I've santized by simply changing text strings. Otherwise, its same data im working from.

Essentially, if you find each instance of "Page: 000X" (X is a variable, obviously), then go up one row, and then select and delete that plus 8 rows, it'll create a consistent, standard format. I appreciate your help.

I wiped all macros from this upload, as anything I had is mostly meaningless at this point.

The goal: every instance of a cell containing the word "Page" in column C will cause excel to move up 1 row, then delete the next 9 rows.

Example:

Cell C20 has "Page: 0006"
Excel recognizes this, then selects row 19:27 and deletes them.
Loops back around until its at the bottom of the dataset and stops

I have several code snippets here and there that would do some of these functions, but im not strong in VBA and don't get how to set variables, utilize offsets, and a few other elements needed here. The dataset is very irregular....but this 9 row rule seems to be how i differentiate a new page header.

Re: VBA search column for #N/A and allow text box replacement of each instance

Thanks for that....i found the issue and rewrote it to flow. Did some error testing, and assuming HR stops making changes to report formats, it should work like a charm. Thanks again.

Re: VBA search column for #N/A and allow text box replacement of each instance

Nailed it, sir (or ma'am). Fantastic. This allows me to clear 2 projects today that have taken a ton of my time.

Thank you kindly. It works just like i need/want.

Re: Need To Paste Into First Blank In Range

You know, i had that solution but tried to find a work around. I have "dummy data" that takes up the first 94 rows (to allow a pivot sheet to display a full calendar month for linking purposes...its too much data for sumproduct arrays), and i prefer to keep them hidden. That command will not account for hidden rows, and it seems the loops required to do that is more that im willing to invest.

Anyway, in retrospect it doesn't matter. Once the macros are in place I can try to do some hide or unhide functions, or just not worry about it as its more about cosmetic than function.

Thank you for your help.

Re: Need To Paste Into First Blank In Range

Quote from sktneer;789209

It should work except your copy line of code is not correct and it should be like this....

Code

Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Copy

That code is visually identical to what I had.

The issue I think im having is with this:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
 
Set CurrentBook = ActiveWorkbook
Worksheets("Balance Check Paste").Activate
ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

I error on

ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select

However I can simply click in the cell I want and paste the data that was copied in the workbook active in the macro.

Re: Need To Paste Into First Blank In Range

THis code will do everything except find the last cell and paste whats on the clipboard:

Sub Daily_Update()'
'###-Import data from PMS export sheets-###
'
   Application.ScreenUpdating = False
   'Import Balance Check Data
   
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook




Set CurrentBook = ActiveWorkbook
MsgBox ("Please select file with Balance Check data for import")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Range("A2:E2" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
End With




Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True




Set CurrentBook = ActiveWorkbook
Worksheets("Balance Check Paste").Activate
ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste








End Sub

Seems like this has been answered before. But i've spent a few hours trying to retrofit, and keep getting compile errors. Long story short: when the macro activates it needs to allow the user to navigate to a workbook, open that workbook, copy the data available, and paste into the first empty cell in cell A95 on the "Balance Check Paste" tab of the original workbook. Ideally, it closes the file it opened once complete (i haven't gotten that far yet).

The code i have will do everything up to pasting it into the workbook. I found a solution that could do the last blank cell in column A, but i have hidden cells that help hold places in a pivot sheet, and it would just overwrite those. So i went to a range and have hit a brick wall.

My code:

Sub Daily_Update()'
'###-Import data from PMS export sheets-###
'
 'Import Balance Check Data
 
Application.ScreenUpdating = False
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook




Set CurrentBook = ActiveWorkbook
MsgBox ("Please select file with Balance Check data for import")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Range("A2:E2" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
End With








Set CurrentBook = ActiveWorkbook
With CurrentBook.Sheets("Balance Check Entry").Range("a95").CurrentRegion
     LR_wbSelectNew = .Rows(.Rows.Count).Row
 End With
  












End Sub

Re: VBA search column for #N/A and allow text box replacement of each instance

Employees who are termed should be a pain to deal with, as it means we are paying for employees who are not paying in themselves, and no longer work with us (someone in HR needs to cancel the policy)

The names....i've had to do quite a bit of workaround to account for a lack of fuzzy logic capability in excel (short of using the add in, which won't work for everyone). One invoice has a space after the comma, one doesn't. One invoice includes middle initial for some, others don't. One invoice has an extra space at the end, and another (for just one employee only) duplicates the middle initial. None seem to match the Roster data.

Im unsure how you would go about doing what you mention, so am unsure how interested i'd end up being. But given you have more experience with it, i would hate to not considering it.

Something else to consider: the invoicing is done as they are delivered. So someone may prepare an invoice for UHC today, then update the book with Guardian in 4 days when its delivered. Each one needs to be input, coded, printed, and entered into our AP system (this last step is outside Excel, obviously), with the workbook having a complete record at month end to be saved for posterity. So each invoice and distribution should stand alone to accomdate this and save paper.

ETA: ideally ill share my workbook with any other location so life gets easier for them. So if you happen to have a better way to look up name data im all about it. There is one kludge for any name that has a space within it (like a hyphenated last name missing the hyphen, as that invoice doesn't print hyphens). You can see it in a formula that denotes a statement for cells containing "STEED"

Re: VBA search column for #N/A and allow text box replacement of each instance

i feel fairly helpless asking for help like this. My hope is i can use the code to broaden general understanding, beyond the task you're helping with.

I've scrubbed it all down to just made up names and whatnot. All prior macros and UDFs are in place.

For what its worth....the GL code is really all the matters, everything links up by GL code. To make the workbook cleaner I can just index match the department description on whats in GL. Save a step on the input box. alternately, picking department can allow the GL to index match, making it easier for those who aren't speaking our GL code language.

Re: VBA search column for #N/A and allow text box replacement of each instance

I do have a question....how would i integrate this so it works on other worksheets? My workbook uses 4 sheets for this, and i'd like to run the same routine from a 5th tab on each. I'd assume if i knew how to name the worksheet, i could just change the column variables and it would work.

Re: VBA search column for #N/A and allow text box replacement of each instance

That nails it +10. Is there a feedback feature or something?

I do a lot of free artwork for people, and understand the labor of love. But still...if there is a feedback feature or something I should use, id be happy. You delivered more than asked.

Re: VBA search column for #N/A and allow text box replacement of each instance

Quote from bryce;789091

You might find it will be better/faster to simply provide the logic to replace any errors during run-time rather than pausing to get user input at each error. Looking at the title of your thread, it seems that that what you want to do. If you can provide more info...even a sample...I can help you with this.

I’ve scrubbed the attached of anything identifying. I’ve left a UDF that I used to help with the logic functions on matching up names and attached it here.

What I would like:

A routine that will check down column N, and when it finds an error pops up a box showing the contents of column A and a prompt to enter a correct GL code.

The #N/A errors typically related to an employee who was termed prior to the parameters on the Roster, or has had their name changed. I pasted values over any formulas on the Roster tab to make sure it didn’t mess up calculations once extracted from the workbook.

It can’t decide what to do on its own due to their being no point of reference. The AP clerk will need to spend time looking up the employee (unless they know them), and coding it onto the attached. The added benefit: any #n/a’s means insurance is being paid for unnecessarily.

Does this make sense?

Re: VBA search column for #N/A and allow text box replacement of each instance

The errors are the result of index/match formulas, somewhere down the chain. Column A would be employees. Column B would GL codes. If they're termed, they won't be on the Roster that im using as reference, so i want to be able to hardcode the GL code into column B (in this example).

There are multiple columns of data that links across and whatnot, and several other VB routines that do stuff like printed, formula writing, etc. So whatever we hardcode into the workbook will be overwritten using VBA scripts the next time the workbook is used. BUt i want people who don't know anything about excel to be able to process this. "Idiot proof" it, so to speak. Along with getting better at using Excel. Outside VBA there's nothing I can't do. Inside VBA, im stuck using my 1985 BASIC programming background to fumble through it.

SHould add: id prefer the routine continue once the value is updated in the text input box, so it works through the list and removes any errors that are present by the end of the exercise

Howdy folks.

Im working on some autocorrecting stuff, and would like to see if I can get input here. |I suppose my biggest issue is lack of familiarity with VBA syntax. Im able to reverse engineer most scripts as needed (i use you guys quite a bit, and just registered today).

Im needing to be able to correct #N/A errors by scanning down a column, and with each instance reference the corresponding cell in a different column as a prompt for overwrite in that cell. For example, after clicking a button to activate the macro in one worksheet, in another it finds each #N/A in column B, it will show an error message like "Update code for (name in column A)" and allow a text box entry to update it:

[TABLE="width: 157"]

Name

Code

Smith, Sara

[TD="align: right"]223[/TD]

Collins, Jeff

[TD="align: center"]#N/A[/TD]

Blow, Joe

[TD="align: right"]453[/TD]

Howard, Duck

#N/A

Little, Debbie

316

[/TABLE]

The rest of the routing rewrites the formulas down that column, but would like to error proof the process afterwards.