Free Microsoft Excel 2013 Quick Reference

Formatted date to a numbered Results

Hi folks, (using Excel 2010 / Win 7) I have vba code in which date values are stored in an array of type variant. These values are published to a new worksheet using the resize property of the Range object: eg:

	VB:
	
(myArray(), 2))) 
 
rng.Value = myArray    rng.Value = myArray 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
On one computer (used for testing the code) it publishes the date-value according to the local-computer’s date format. E.g. 3/02/2012 (this is what I want)

- The problem is, that on another computer, however, it publishes the date format as a serial number e.g. 40942.

Note YYYYMMDD date values are calculated and stored in the VBA Array according to the commands below, so the date-values are stored in the array in the date-format of the local computer (Variant/Date e.g. #02-12-2010#) .


	VB:
	
yearX = Left(Trim(dateY), 4) 
 
monthX = Mid(dateY, 6, 2) 
dayX = Mid(dateY, 9, 2 
dateY = DateSerial(yearX, monthX, dayX) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As far as I can determine, the regional settings on both computers are the same. Also, the worksheets are new so they have no pre-formatted cells. Many thanks in advance for your advice,
Peter.

Hi

I've gone throught the internet trying to find a solution, and I can't see whats wrong with the code. The below code copies some specific data from various sources to a sheet called "Grafer 2".

The code does that just fine, but then when it gets to the part about determining what month the cell B6 is, I get an error. The error is "object required".

I would like the code to do the following:

If Month(Ldate) is not December, then it must shift the range "OutstandingData" down a number of rows equal to the month we're in (ie. for September, 9 rows). It should then clear the contents of those rows and set the values equal to the top row of the range "OutstandingData".

The logic is the following:

If the calculation date (CalcDate) is not in December, then we need to create extra rows and put in "fake" data that equals the top row of the "real" data. The real data is described by the range "OutstandingData".

Hope someone can help!


	VB:
	
 
 
wb.Worksheets("Grafer 2").Range("OutstandingData").ClearContents 
wb.Worksheets("Grafer 2").Range("OutstandingData").Value = wb.Worksheets("Outstanding Amount").Range("B3:D305").Value 
wb.Worksheets("Grafer 2").Range("B6").Value = wb.Worksheets("Start").Range("CalcDate").Value 
 
Set Ldate = Range("B6").Value 
 
Month(Ldate) = Dmonth 
 
If Dmonth  12 Then 
    Range("OutstandingData").Offset(Dmonth, 2).Value = Range("OutstandingData").Value 
    Range("OutstandingStart").Offset(Dmonth - 1, 2).ClearContents 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Okay so I have been stumped for multiple days by this:

I am currently working on a backlog report for my company, we manufacture waveguides. The way the sheet is set up there is a part number, the customer, and the listed status of the part. There is then a very thin black column that seperates this from the next section of the sheet, which is where the conditional formatting takes place. Every possible status of a given part is a column title, IE:"DIP BRAZE", these are vertically written and are all of uniform size. I have the Status list from the first section of the page linked to another sheet in the workbook that is a raw dump, where i copy our information off the internet and simply paste it into the book. I dont want to link to an external database or any of that so please dont try to tell me to. Anyways, depending on the status of the item a green bar will fill out to a certain point along the columns of status, like a progress bar. This system is working flawlessly, the problem happens because i want the bar to turn red if it gets behind schedule, which I also know how to do. The problem is that every step of the status has a different setback date, and there are > 20 different statuses. I already coded all of these into conditional formatting for one row(one part). The way that I am comparing the dates is that the due date of the part requested by the customer is pulled from the raw data dump, and I am using

=IF(AND($AP4-TODAY()

Hi, I've searched the forum and found solutions to a similar but not exactly posed as my question so apologies if this is redundant but my experience with VBA is quite elementary.

I would like to insert new columns (in between two already created columns) on a worksheet. The new columns will start after a column "F" which has a header "Start Date" in Date format. The number of columns to be be created will be equal to the value of (n) - 1, where n represents a duration of the current forecast.

Each successive column will have a header equal to successive dates following the start date...so Column G would be equal to "Start Date +1", Column H would be equal to "Start Date +2) up to "Start Date + n - 1".

The other constraint is the set of already defined columns that the new columns will be inserted before and ensuring that they are not overwritten.

I saw a solution with the code below however some of the defined constants (endstart, Total etc. ) are not applicable.
Will appreciate if one could advise. I've attached a table showing the desired result.

Try this search: Explicit

Hi folks,

Hoping you can help me here.

I have a list of usernames (A:A) and in D:D to AP:AP I have the dates for 40 different events which happen sequentially (i.e AP>AO>AN...>D is always true). I've named this range (D:AP) as level and it re

In B:B I have another date event which might happen at any time (so isn't sequential to level) and this is purchase. There is only ever one purchase event per user (for simplicity's sake)

In column C, I'd like to return the column number from level for which level > pay for the first time.

For example, a user has the following level events:

Level One: 1/1/12
Level Two: 5/1/12
Level Three: 2/2/12
Level Four: 8/2/12

and the same user has purchase event as 5/2/12.

Not all users will have reached all levels, which means that sometimes, columns in level will be blank.

I'd like column C to then return 'Event Three', since a users level when they make a purchase is 'three'.

Where I've made reference to a date above, I'm referring to a DATETIME format of dd/mm/yyyy hh:mm:ss

Can anybody help? If purchase was equal to level I'd use some variant of a SUMPRODUCT, but I'm happy to accept either a formula or a VBA solution because I've been banging my head against a wall thus far.

edit: attached sample file.

i have textboxes on a user form (five for each weekday) user enters numbers then sends to worksheet (command butoon click event) they can also call up the week date(the text boxes will auto populate ) they can then edit existing data or add a new days worth. the code i have works fine if you edit, if i add new it also works, but if i try to add new and edit, it adds a duplicate date to the next line on the worksheet. how can i get the code to both add something new to the next row as well as allow changes to existing. i am missing something. thanks here is a code snipet showing both codes i am using


	VB:
	
 EDITAPPTADD_Click() 
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Dim ws2    As Worksheet 
    Dim lastrow As Long 
    Dim r As Long 
    Dim irow As Long 
    Set ws2 = ActiveWorkbook.Worksheets("APPTdata") 
    lastrow = ws2.Range("A500").End(xlUp).Row 
    irow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
     
    For r = 1 To lastrow 
        If ws2.Cells(r, 1).Value = DateValue(Me.editfridate.Text) Then 
             'copy FRIDAY data to the database
             
            ws2.Cells(r, 4).Value = EDITFRIPRI.Value 
            ws2.Cells(r, 5).Value = EDITFRITOT.Value 
            ws2.Cells(r, 6).Value = EDITFRIDNR.Value 
            ws2.Cells(r, 7).Value = EDITFRIBMS.Value 
            ws2.Cells(r, 8).Value = EDITFRICON.Value 
            ws2.Cells(r, 9).Value = EDITFRIPRES.Value 
            ws2.Cells(r, 10).Value = EDITFRIPROS.Value 
            ws2.Cells(r, 11).Value = EDITFRISALES.Value 
            ws2.Cells(r, 12).Value = EDITFRIHYBRID.Value 
             
             'add new appt to list
             
            If Not ws2.Cells(r, 1).Value = DateValue(Me.editmondate.Text) And _ 
            Me.EDITMONTOT.Value > "" Then 
                 
                ws2.Cells(irow, 1).Value = Me.editmondate.Value 
                ws2.Cells(irow, 4).Value = Me.EDITMONPRI.Value 
                ws2.Cells(irow, 5).Value = Me.EDITMONTOT.Value 
                ws2.Cells(irow, 6).Value = Me.EDITMONDNR.Value 
                ws2.Cells(irow, 7).Value = Me.EDITMONBMS.Value 
                ws2.Cells(irow, 8).Value = Me.EDITMONCON.Value 
                ws2.Cells(irow, 9).Value = Me.EDITMONPRES.Value 
                ws2.Cells(irow, 10).Value = Me.EDITMONPROS.Value 
                ws2.Cells(irow, 11).Value = Me.EDITMONSALES.Value 
                ws2.Cells(irow, 12).Value = Me.EDITMONHYBRID.Value 
                 
            End If 
             
        Next r 
         
         
        Me.EDITFRIPRI.Value = "" 
        Me.EDITFRITOT.Value = "" 
        Me.EDITFRIDNR.Value = "" 
        Me.EDITFRIBMS.Value = "" 
        Me.EDITFRICON.Value = "" 
        Me.EDITFRIPRES.Value = "" 
        Me.EDITFRIPROS.Value = "" 
        Me.EDITFRISALES.Value = "" 
        Me.EDITFRIHYBRID.Value = "" 
         'clear THURSDAY data

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I know how to add to dates generated by excel ie"today" but I am having problems adding to a manually added date. I really only need 3 cells example a1 to contain 1,2,3 or 4 this being years, cell a2 date entered by me (usually in 22/04/12 format) Cell a3 to have A1 (number of years) added to Cell A2. Any help appreciated.

Hi everyone,
I have another bender that has had me scratching my head for the last few days. I am trying to avoid VBA and for the life of me I can't figure this one out.

So, my problem is this:
I have a scheduling sheet which basically has dates in the top row, in the left most column it has tasks (and beside that the name of the person who can carry out the task), at the interestion of the 2 (date and task) it MAY have a value (1-12) representing the number of hours taken to carry out the task.

Now I am trying to generate a report basically from date and name of person. So they know what they have to do on a particular date.

So so far I have a list box for the date, a list box for the name of the person. I am trying to get excel to look at the column (date selected) and look down the column until it finds a number (any number at all) - this means the task has to be performed on that date. Then look across and see if the persons name is there - if so return the task.

I am pretty much stuck on the bulk of it, if I could get it to work even to look down the relevant date column for number (1 to 12) and return the relevant task and the name this would be a massive help then I could just place that into a sheet and vlookup the persons name from there - so it does not have to be all in the one cell/sheet I can spread this over some sheets if need be. Example below:


	VB:
	
__________________________|..9/07/2012..|..16/07/2012...|...23/07/2012...| 
Wash car.........|.Jeff...|....2........|...............|......2.........| 
Clean windows....|.Fred...|.............|...............|......1.........| 
check tyres......|.Jeff...|.....4.......|...............|................| 
check oil........|.Jeff...|.............|.......10......|................| 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So the output for 23/07/2012
"Wash car"
"Clean Windows"

A perfect solution would be the option to also choose the persons name also: 23/07/2012 :Jeff
"Wash Car"

Sadly the only thing I seem to have working is:

=MATCH(TRUE,Schedule!$AV$9:$AV$3000 "",0)

Entered as an array which seems to find the first time a number is used in the column and return the row number. From this I can get the first row of the report generated but I don't know how to keep going ... i.e find the "nth" relevant task in the list and place it in the report. I don't know how to proceed from here I have tried index/match/vlookup/hlookup.. I may just be confusing myself.

I also have this which helps find the task:

=INDEX('Schedule'!$A$9:$A$3000,MATCH(TRUE,'Schedule'!$AV$9:$AV$3000 "",0))

That said I think the 'Schedule'!$AV$9:$AV$3000 should actually reference the "DATE" selected so it has to be able to change (variable)
Sheet is fairly large - 3000 tasks at last count, spread over 3 years in columns

Apreciate any and all help, even guesses or partial solutions

Cheers,
Lag

Every day I run a macro.

Every day the macro leaves open a source file (it copies info into a new workbook).

I can't reliably close the source file within the Macro because the source file has a naming format like

VendorBacklog4343242343 (the numbers aren't a date or anything I can predict). Today's was 82435386 - which I hoped might be Unix time, but this is 1972 in Unix time.

Anyway I can refer to the file as "filebeginning(VendorBacklog).close" or some such?

THANKS

I have been working on a VBA code and am stuck on the conditional formatting step. I have copied the code below for review. What I would like to do is have the JobType determine the color of the cell by using conditional formatting. For example, JobType paid = green, warranty = red, tenative = blue, in house = yellow, and then one default for anyother type. I am not sure how to go out this, so any help would be appreciated. Thanks!


	VB:
	
 Button1_Click() 
     'Job Scheduling Button
    Dim mySelection As Range 
    Dim placeHolder As Range 
    Dim JobNumber As String 
    Dim Customer As String 
    Dim Location As String 
    Dim JobType As String 
    Dim allInfo As String 
     
    Set mySelection = Application.InputBox(prompt:="Select the Range of Dates to Re-Schedule", Type:=8) 
    mySelection.Select 
    mySelection.UnMerge 
    mySelection.Interior.Color = RGB(255, 255, 255) 
    mySelection.ClearContents 
    mySelection = "Available" 
    mySelection.Borders.LineStyle = xlHairline 
     
    Set placeHolder = Application.InputBox(prompt:="Select the New Range of Dates", Type:=8) 
     
    JobNumber = Application.InputBox(prompt:="What is the Project Number?") 
    Customer = Application.InputBox(prompt:="What is the Customer Name?", Type:=2) 
    Location = Application.InputBox(prompt:="What is the Customer Location?") 
    JobType = Application.InputBox(prompt:="What is the Job Type?") 
    placeHolder.Select 
    placeHolder.ClearContents 
    placeHolder.Merge 
     
    allInfo = JobNumber & " - " & Customer & " " & Location & " " 
    placeHolder = allInfo 
    mySelection.Select 
    placeHolder.HorizontalAlignment = xlCenter 
    placeHolder.Interior.Color = RGB(155, 187, 89) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a simple workbook in 2007, i.e. basic formula, a few tabs, no macros. Some items are formatted to custom date format mmm-yyyy, some to standard number format no decimal places, no comma separators, and some to standard number format two decimal places, comma separator.

Every time the file is closed the formats all revert back to General, i.e. the date formats go back to the Excel date code and the number formats lose all decimal points, etc. This doesn't happen when the file is just saved, only when it is either saved and then closed or closed and 'yes' to save is selected.

I've never had this happen before and it's extremely frustrating to have to go through and change all these every time.

Any clues as to how to fix this would be very much appreciated!!

Edit: So I haven't stopped looking up how to do this but whenever I find anything on it I get lost in how it works and how to implement it properly in my spreadsheet.

So there is a lot going on in this new userform for my inventory.

I need to create a userform with code that will allow me to search by serial number (which is typed by the user) and part type which is specified in a combobox which is already set up when the userform initializes:

	VB:
	
 UserForm_Initialize() 
     'initialize the form and set the date time label
    With combosearchparttype 
        .AddItem "Select Part Type" 
        .AddItem "Bill Validator" 
        .AddItem "CPU Tray" 
        .AddItem "Monitor" 
        .AddItem "Power Supply" 
        .AddItem "Printer" 
        .AddItem "Misc" 
    End With 
    combosearchparttype.Value = "Select Part Type" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Which then sets the worksheet (Which we will be searching in):

	VB:
	
 buttonsearch_Click() 
    Dim ws As Worksheet 
     'set the worksheet for data dump
    If combosearchparttype = "Select Part Type" Then 
        MsgBox "Please Select Part Type" 
        Exit Sub 
    ElseIf combosearchparttype = "Bill Validator" Then 
        Set ws = Worksheets("Bill Validator") 
    ElseIf combosearchparttype = "CPU Tray" Then 
        Set ws = Worksheets("CPU Tray") 
    ElseIf combosearchparttype = "Monitor" Then 
        Set ws = Worksheets("Monitor") 
    ElseIf combosearchparttype = "Power Supply" Then 
        Set ws = Worksheets("Power Supply") 
    ElseIf combosearchparttype = "Printer" Then 
        Set ws = Worksheets("Printer") 
    ElseIf combosearchparttype = "Misc" Then 
        Set ws = Worksheets("Misc") 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The next part is where the found data will be put, which is text boxes labeled as:

	VB:
	
textresultmanufacturer 
textresultmodel 
txtresultserialnumber 
textresultvendor 
textresultlocation 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
We do not need a list box in this situation since we are going to be searching by a unique number that can only be associated to one part.

The last part (Which is submitting the data to a different worksheet known as "checking the part out") is something I can code relitively easily, the only part I do not know how to code is the search function.

Edit (Edit was solved)

Edit 2: This is now the final code minus the search function:

	VB:
	
 UserForm_Initialize() 
     'initialize the form and set the date time label
    With combosearchparttype 
        .AddItem "Select Part Type" 
        .AddItem "Bill Validator" 
        .AddItem "CPU Tray" 
        .AddItem "Monitor" 
        .AddItem "Power Supply" 
        .AddItem "Printer" 
        .AddItem "Misc" 
    End With 
    combosearchparttype.Value = "Select Part Type" 
    With comboresultparttype 
        .AddItem "Select Part Type" 
        .AddItem "Bill Validator" 
        .AddItem "CPU Tray" 
        .AddItem "Monitor" 
        .AddItem "Power Supply" 
        .AddItem "Printer" 
        .AddItem "Misc" 
    End With 
    comboresultparttype.Value = "Select Part Type" 
    With combosubmittransactiontype 
        .AddItem "Select Transaction Type" 
        .AddItem "Check Out Good" 
        .AddItem "Check In Bad" 
    End With 
    combosubmittransactiontype.Value = "Select Transaction Type" 
    labelsubmitdatetime.Caption = Now 
End Sub 
Private Sub combosubmittransactiontype_Change() 
    With Me 
        Select Case .combosubmittransactiontype.ListIndex 
             'Change To/From Label and Checked In/Out Label
        Case 0 
            .framesubmit.Caption = "Check In/Out Information" 
            .labelsubmittofromlocation.Caption = "To/From Location" 
            .labelsubmitcheckedoutby.Caption = "Checked In/Out By" 
            .labelsubmitissuedescription.Visible = True 
            .textsubmitissuedescription.Visible = True 
        Case 1 
            .framesubmit.Caption = "Check Out Information" 
            .labelsubmittofromlocation.Caption = "To Location" 
            .labelsubmitcheckedoutby.Caption = "Checked Out By" 
            .labelsubmitissuedescription.Visible = False 
            .textsubmitissuedescription.Visible = False 
        Case 2 
            .framesubmit.Caption = "Check In Information" 
            .labelsubmittofromlocation.Caption = "From Location" 
            .labelsubmitcheckedoutby.Caption = "Checked In By" 
            .labelsubmitissuedescription.Visible = True 
            .textsubmitissuedescription.Visible = True 
        End Select 
    End With 
End Sub 
Private Sub comboresultparttype_Change() 
    With Me 
        Select Case .comboresultparttype.ListIndex 
             'Changed visibility of Misc Description
        Case 0 
            .labelsubmitmiscdescription.Visible = True 
            .textsubmitmiscdescription.Visible = True 
        Case 1 
            .labelsubmitmiscdescription.Visible = False 
            .textsubmitmiscdescription.Visible = False 
        Case 2 
            .labelsubmitmiscdescription.Visible = False 
            .textsubmitmiscdescription.Visible = False 
        Case 3 
            .labelsubmitmiscdescription.Visible = False 
            .textsubmitmiscdescription.Visible = False 
        Case 4 
            .labelsubmitmiscdescription.Visible = False 
            .textsubmitmiscdescription.Visible = False 
        Case 5 
            .labelsubmitmiscdescription.Visible = False 
            .textsubmitmiscdescription.Visible = False 
        Case 6 
            .labelsubmitmiscdescription.Visible = True 
            .textsubmitmiscdescription.Visible = True 
        End Select 
    End With 
End Sub 
Private Sub buttonsearch_Click() 
    MsgBox "Search Function Coming Soon!" 
    Exit Sub 
     'Dim ws As Worksheet
     'set the worksheet to search
     'If combosearchparttype = "Select Part Type" Then
     'MsgBox "Please Select Part Type"
     'Exit Sub
     'ElseIf combosearchparttype = "Bill Validator" Then
     'Set ws = Worksheets("Bill Validator")
     'ElseIf combosearchparttype = "CPU Tray" Then
     'Set ws = Worksheets("CPU Tray")
     'ElseIf combosearchparttype = "Monitor" Then
     'Set ws = Worksheets("Monitor")
     'ElseIf combosearchparttype = "Power Supply" Then
     'Set ws = Worksheets("Power Supply")
     'ElseIf combosearchparttype = "Printer" Then
     'Set ws = Worksheets("Printer")
     'ElseIf combosearchparttype = "Misc" Then
     'Set ws = Worksheets("Misc")
     'End If
End Sub 
Private Sub buttonsubmit_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
     'Set spreadsheet for data dump
    If combosubmittransactiontype = "Select Transaction Type" Then 
        MsgBox "Please Select Transaction Type" 
        Exit Sub 
    ElseIf combosubmittransactiontype = "Check Out Good" Then 
        Set ws = Worksheets("Outgoing Good") 
    ElseIf combosubmittransactiontype = "Check In Bad" Then 
        Set ws = Worksheets("Incoming Bad") 
    End If 
     
     'find first empty row
    iRow = ws.Cells(Rows.Count, 1) _ 
    .End(xlUp).Offset(1, 0).Row 
     'Part Type and Misc Description Cells
    If comboresultparttype = "Select Part Type" Then 
        MsgBox "Please Select Part Type" 
        Exit Sub 
    ElseIf comboresultparttype = "Bill Validator" Then 
        ws.Cells(iRow, 1).Value = "Bill Validator" 
        ws.Cells(iRow, 2).Value = "N/A" 
    ElseIf comboresultparttype = "CPU Tray" Then 
        ws.Cells(iRow, 1).Value = "CPU Tray" 
        ws.Cells(iRow, 2).Value = "N/A" 
    ElseIf comboresultparttype = "Monitor" Then 
        ws.Cells(iRow, 1).Value = "Monitor" 
        ws.Cells(iRow, 2).Value = "N/A" 
    ElseIf comboresultparttype = "Power Supply" Then 
        ws.Cells(iRow, 1).Value = "Power Supply" 
        ws.Cells(iRow, 2).Value = "N/A" 
    ElseIf comboresultparttype = "Printer" Then 
        ws.Cells(iRow, 1).Value = "Printer" 
        ws.Cells(iRow, 2).Value = "N/A" 
    ElseIf comboresultparttype = "Misc" Then 
        If Me.comboresultparttype.Value = "" Then 
            MsgBox "Please Describe The Part" 
            Exit Sub 
        Else: ws.Cells(iRow, 2).Value = Me.comboresultparttype.Value 
        End If 
        ws.Cells(iRow, 1).Value = "Misc" 
    End If 
     'Dump the rest of the data
    ws.Cells(iRow, 3).Value = Me.textresultmanufacturer.Value 
    ws.Cells(iRow, 4).Value = Me.textresultmodel.Value 
    ws.Cells(iRow, 5).Value = Me.textresultserialnumber.Value 
    ws.Cells(iRow, 6).Value = Me.textsubmittofromlocation.Value 
     'Issue Description Handling
    If combosubmittransactiontype = "Check Out Good" Then 
        ws.Cells(iRow, 7).Value = Me.textsubmitcheckedoutby.Value 
        ws.Cells(iRow, 8).Value = Me.labelsubmitdatetime.Caption 
    ElseIf combosubmittransactiontype = "Check In Bad" Then 
        ws.Cells(iRow, 7).Value = Me.textsubmitissuedescription.Value 
        ws.Cells(iRow, 8).Value = Me.textsubmitcheckedoutby.Value 
        ws.Cells(iRow, 9).Value = Me.labelsubmitdatetime.Caption 
    End If 
     'Clear Data After Dump
    comboresultparttype.Value = "Select Part Type" 
    combosubmittransactiontype.Value = "Select Transaction Type" 
    Me.textresultmanufacturer.Value = "" 
    Me.textresultmodel.Value = "" 
    Me.textresultserialnumber.Value = "" 
    Me.textsubmittofromlocation.Value = "" 
    Me.textsubmitissuedescription.Value = "" 
    Me.textsubmitcheckedoutby.Value = "" 
    labelsubmitdatetime.Caption = Now 
End Sub 
Private Sub buttoncancel_Click() 
    Unload Me 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
InventoryTest.xlsm

I am trying to remove selected strings of text and the date associated with them from a large number of customer account records.

For example, I have the following text in a single cell;

09/02/2011 DRNAgencyProcess CLOSE NB1X1691 5 ACMCLS RECALLED BY CLIENT 08/20/2010 Agent Forced to close case 08/13/2010 Agent no acct to transfer, no unposted payment,called customer thru number 780-691-3164 as per recording number is not recognized mailbox.updated to 111-1111.no other number listed.future credit events already in place./u2ds 06/12/2010 Agent Event Status Date Changed to 2010-06-11. Event Status Changed to Completed . 06/12/2010 Agent Create Event

Many of the comments are system generated and/or repetitive and I am trying to strip them out. To look at the comments individually they look like this;

09/02/2011 DRNAgencyProcess CLOSE NB1X1691 5 ACMCLS RECALLED BY CLIENT
08/20/2010 Agent Forced to close case
08/13/2010 Agent no acct to transfer, no unposted payment,called customer thru number xxx-xxx-3164 as per recording number is not recognized mailbox.updated to 111-1111.no other number listed.future credit events already in place./u2ds
06/12/2010 Agent Event Status Date Changed to 2010-06-11. Event Status Changed to Completed
06/12/2010 Agent Create Event

The only comment I want to keep from the above example is the one starting with "08/13/2010 Agent no acct to transfer...". The rest I want to delete. I am currently using Replace to remove repetitive comments like "Agent Create Event" but I can't figure out how to remove the date associated with it so I end up with a bunch of dates with no text between most of them. I tried modifying the code below from another post but could not get it to work.


	VB:
	
) 
    Dim cell    As Range 
     
    For Each cell In r 
        With cell 
            If IsDate(.Value) And Not .HasFormula Then 
                If Int(.Value) = da1 Then 
                    .Value = .Value - da1 + da2 
                End If 
            End If 
        End With 
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Using Excel 2007. The macro is saved in my PERSONAL.XLSB file. A user will open an existing XLS which could
be located in any number of different directories.

The macro will copy and then paste Values to a new Workbook. When the different changes are completed I do
the SaveAs command. I want it saved in the directory of the existing XLS file with the name copying cell B2
and adding the Format(Now, "HHMMSS") & ".xlxs".

My only problem is getting it to save in the same directory as the existing XLS and not in the directory as
the PERSONAL.XLSB. Would I have to perform a command while in the existing XLS before doing the copy?
Many thanks, Bob


	VB:
	
 OldTS() 
     '
     ' OldTS Macro
     '
     ' This will turn OFF the screen updating
    Application.ScreenUpdating = False 
     
    Dim Savename As String 
     
     ' This will copy the shown range and open a new workbook.
    Range("B3:W25").Select 
    Selection.Copy 
    Workbooks.Add 
     ' This section will only paste the VALUES of the copied cells and not formulas.
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Range("A1").Select 
    Application.CutCopyMode = False 
     
     ' >>> I removed the misc information about deleting, entering text etc.
     
     ' This will SaveAs to the CURRENT folder name it OldTS and add the END DATE to the name.
     '      SaveAsName = ThisWorkbook.Path
    Savename = ActiveSheet.Range("B2").Text 
    ActiveWorkbook.SaveAs Savename & "-" & Format(Now, "HHMMSS") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, 
    CreateBackup:=False 
     'This will turn ON the screen updating
    Application.ScreenUpdating = True 
     'This will close the new file and return to the original file.
    ActiveWindow.Close 
    Range("B3").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello people

I am just trying to format cells in A column depending on if cells in column B includes any date from month 07 and 08 for the same order number.

Example is attached. Sheet 1 is the raw data, Sheet 2 is what i want to succeed as a result using conditional formatting.

Can you lend a hand ?

Hello

Column A is user input. In Column B a Lookup, lookups the users inputted value and returns text, not numbers. Column B will contain duplicates. I need a conditional formatting statement to highlight these duplicates.

The problem is, the lookup formula in columm B:

	VB:
	
),"")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If the user has yet to input a value then nothing, (A1="",""), will happen. This, for some strange reason, puts some hidden value in the cell that make the following conditional formatting function useless.


	VB:
	
1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If the lookup in column B returns a numeric value then this conditional formatting function works.


	VB:
	
1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Unfortunately it will never return a numerical value, always text. SO How do i create a conditional formatting function to highlight duplicates in column B when the column will contain text? hope this makes sense!

SOLVED

	VB:
	
1,"") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
:-)

~Hello. I'm adding a dashboard page to a complex tracking worksheet (macro-enabled, shared and protected Excel 2007). Where I'm stuck is trying to autopopulate a row showing a count of the number of dates in a ReviewDate column that fall between certain dates only if multiple criteria are met. I've attached a sample spreadsheet and mocked up someting below.

Sample excel file
Dash.xlsm

The results will be on a dashboard page needs to have nicer (brand standard) formatting for senior leaders, which makes Pivot Tables difficult to use. The spreadsheet is not that large so calc times are not a concern (yet). I've tried COUNTIF, COUNTIFS, SUMPRODUCT and everything else I could think of and have struck out. I think my main problem is the stringing the complex syntax together properly.

Your expertise and suggestions are appreciated! If I can solve for #1 below, I can figure out the rest I think. Thank you!

Example of source data

ReviewDate Status Approach LOB 7/23/2011 Not Started ILT A Finance 7/25/2011 In Progress ILT B Finance 7/26/2011 Develop WBT A Tech 7/23/2011 Not Started WBT B Finance 7/25/2011 Complete WBT B Finance 7/26/2011 Complete ILT B Finance 7/29/2011 Develop ILT B HR 7/31/2011 In Progress ILT A Finance 8/2/2011 Not Started WBT A Finance

What I'm trying to figure out is:ILTs Due = COUNT of Approach "ILT A" OR "ILT B" where LOB = "Finance" AND Status = "In Progress" OR "Not Started" AND ReviewDate is between July 11-18, 18-24, 25-31, etc.ILTs Complete = ILT A or ILT B = "Complete" and LOB = "Finance" (just a simple running total)

Example of dashboard table
[Finance]
Week of July 11 July 18 July 25 August 1 August 8 ILTs Due 2 2 1 ILTs Complete 1 WBTs Due 1 WBTs Complete 1

Hi
I have absolutly no idea where to start with this problem, I have a spreadsheet where i need to count 2 coloums, the first contains a number between 1 and 6 or between 7 and 17, the second contains dates in the format dd/mm/yy there is other data in the sheet but for my purpose it is irrelivant,
How can i get excel to count only the dates in the last 12 months, so on the 30/06/11 it counts back to 30/06/10 but on the 01/07/11 it counts back to 01/07/10 and so on (rolling year) AND also only count the numbers in the first coloum for the same period these numbers must be counted as two values, everything between 1 and 6 and everything between 7 and 17

Hi,

I'm fairly new to VBA, and am running into a problem when trying to export my file as a txt file. I need to gather data that includes Date, Time, Zip code, and a place holder that has leading zeros (its just 00001). I have a program set up to take all my data and organize it the way I want, and format it to what I need...but when I have it export as a txt file, the formatting doesn't carry through.

This is what happens: I have data formatted like so:
For Time- "hhmm;@" so, 5:56PM shows as 1756
For Date - "yyyymmdd", so 5/16/2011 shows as 20110516
For Placeholder - "0000#", and the number is 1, so it shows as 00001
For Zip = "0####" - only because if the zip has a leading zero it got rid of it in the process

When i export as a txt file, it goes back to 5/16/2011 for the date, some crazy numbers for the time, just the 1 for the placeholder, and drops leading zeros for the zip code. Is there a way to hardcode the formatting in VBA so it will stay how I want it when exporting as a txt file?

Any help would be greatly appreciated.

Thanks

Hi there,

I have 3 comboboxes, called dayInputBox, MonthInputBox, and YearInputBox on a Form. They contain a list of numbers: 1-31, 1-12, and 2010 -2020 respectively, all populated from a worksheet in excel.
Within my code I also have a variable called DateRangeStart. When I press the search button on the form I want the 3 integers to be taken and converted to a date format dd/mm/yyyy and the DateRangeStart variable populated with this date. This should all happen in VBA.

I am having problems with the conversion of the integers to a date within vba and would welcome any suggestions that you have.

Thanks very much!


No luck finding an answer? You could always try Google.