Free Microsoft Excel 2013 Quick Reference

VBA code to find System (Desktop) date format

i am looking for 'VBA code to find System (Desktop) date format'
Could anyone help me to the code.

I am creating a file which will be used by different users having different sytem date formats i.e mm - dd - yyyy or dd - mm - yyyy &
my file calculation depends on the date calculations based on inputs provided in different formats,


Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.

Buddy

Please note firstly I've post this on different forum but didn't get any solution after many hours of waiting, so can someone help me out here please. Where I've posted: here and here and here

Problem:
I need the VBA code to find the highest and 2nd highest value in a column based on criteria in another column. So for example:
Type | Time
RaceA| 4.5
RaceB| 5.5
RaceA| 6.2
RaceA| 3.1
RaceB| 2.1
I need the VBA code to be able to find the highest and 2nd highest Time for RaceA and highlight them in different color. So in the example above, the code should loop through the time based on Type and highlight 3.1 as highest and 4.5 as second highest
I need the vba sub instead of worksheet function.
Can anyone help pls?

Howdy.

I'm new around here and thought it might be nice to say hi to all before I start.

I've come here in desperation to find an answer I've been seeking for days and days. I've read my beginners VBA book, googled my rear off, but no joy.

Here's my conundrum.

I have a roster sheet for work that displays start and finish times as a date and time, and formated to hh:mm. These start / finish times are scattered throughout several worksheets within 1 workbook.

Thus, the cell displays : 08:00 or 18:00 but the data in the formula bar reads as : 20/09/2005 8:00 AM or 20/09/2005 6:00 PM

What I have been trying to do is create a macro to find all these cells containing a date and time, or even just a date, and refresh the value up by 1 day. To use the example above, I want my macro to find these particular values and change them by 1 day, resulting in 21/09/2005 8:00 AM etc etc.

Being a self taught VBA beginner, this one has proven a tad difficult for me, and was hoping I could pick up the answer and some knowledge along the way from more experienced guys in here.

I'm currently at the end of a long day and must prepare for an early start tomorrow. I'll check back here in the morning.

Thanks in advance.

noddy

http://www.excelforum.com/excel-prog...data-sets.html

Hi All,

I have reported the above question in forum (excelforum.com) with the attachment.

Any help would be greatly appreciated.

Many Regards
Manav

Hi All,

Respected Members please accept my sincere thanks for all your esteemed help given through this channel.

I have attached a sample worksheet which I use in work. My aim is to find job nuumbers on sheet1 in the dataset on sheet 2(column A) and return the corresponding values iolumn B & C on sheet 2. Please note that one job number can be paid more than one time in the past months(eg. 3300023104), and therefore the requirement is to total the amount of money paid in all months, on a particular job reference. The output i require on sheet 1 is "month paid in" and "amount paid ". If a job is paid just once then simply get the month and amount on sheet 1. But i dont know if a paricular job like 3300023104 has been paid loads of times in several months, how can we list the months as well as the amount. If months is not possible then only amount can do...

My objective is to find out how much money has been paid before on the jobs mentioned on sheet 1 column A (my current month invoice). I already have a vba code to trace any duplicates within column A on sheet 1. For instance job ref 3300023104 has been repeated a few times on sheet 1.

Please advise at your convenience.

Thank you
Manav

I currently use some Excel VBA (an edited version of some code posted on ozgrid; thanks to the original author) to copy graphs from different sheets in an Excel workbook to a new Powerpoint presentation (one graph per slide). The graphs in Excel are simple line graphs with weekly dates UK style (dd/mm/yyyy) on the x-axis, and values on the y-axis. All works well when these are copied and pasted into PowerPoint as pictures in their own slides, except that the dates become Americanised (mm/dd/yyyy). Does anyone know what I need to write in the VBA code to preserve the original date formatting? Apologies, but I am unable to attach the Excel and PowerPoint files - I hope the explanation above is sufficient.

I am also posting this in the PowerPoint forum, because I don't know which is the more appropriate location

Martin Benjamin

I have an array A1:F1500 is important to find if any of the rows are repeated, I attache a little example, I don't want use excel functions I want a vba code to do this task, I am using excel 2003. any executable file welcome. thanks.
HTML Code: 
12	6	1	0	2	8
1	9	21	23	17	16
28	35	35	33	26	22
29	28	22	24	22	17
6	4	2	12	26	21
2	5	1	0	4	4
0	1	9	15	24	3
1	12	18	17	22	0
5	18	21	19	23	14
5	10	11	7	7	11
1	6	20	20	19	9
22	19	1	2	3	7
28	29	27	19	5	8
2	4	9	2	16	0
6	4	14	17	10	6
6	7	4	9	11	7
5	9	4	7	10	8
16	23	20	23	7	2
7	3	13	17	3	2
3	21	9	5	5	6
1	21	19	11	8	1
2	14	19	16	11	2
3	12	18	16	13	1
1	4	3	7	0	0
2	4	8	12	11	5
8	8	11	8	1	5
1	3	25	23	21	25
1	1	9	6	25	26
3	11	16	21	2	0
5	18	36	37	18	14
2	16	18	19	1	1
2	10	4	2	0	2
16	18	13	18	15	14
14	16	26	12	12	2
5	2	0	14	11	14
1	1	2	2	16	6
0	5	6	4	8	10
7	19	17	13	5	8
7	13	19	15	2	7
6	5	14	22	11	13
8	12	24	7	10	0
10	27	29	2	9	1
8	23	22	11	13	4




Hi,

I have a task that pulls out information from the website. I need to write a vba code to find the last row. Since the no of rows are always not the same. I would need the excel masters help to solve it. Just a small piece of guidance would also help me.

In my work sheet I need to find the word " Function Name: Cleaning" if this is there then I should find the occurence of the word " name". If I find that then the code should copy the values in the column after name till it sees a space ( means no value). Should copy till that and paste that in another work sheet.

Am confused to consturct the if cases here. Have attached a sample file.

Any help is greatly appreciated.

Thanks in advance for the help.

I currently use some Excel VBA (an edited version of some code posted on ozgrid; thanks to the original author) to copy graphs from different sheets in an Excel workbook to a new Powerpoint presentation (one graph per slide). The graphs in Excel are simple line graphs with weekly dates UK style (dd/mm/yyyy) on the x-axis, and values on the y-axis. All works well when these are copied and pasted into PowerPoint as pictures in their own slides, except that the dates become Americanised (mm/dd/yyyy). Does anyone know what I need to write in the VBA code to preserve the original date formatting? Apologies, but I am unable to attach the Excel and PowerPoint files - I hope the explanation above is sufficient.

I am also posting this in the Excel VBA forum, because I don't know which is the more appropriate location

Martin Benjamin

I am trying to write VBA code to find the first occurrence of #N/A in a range, equivalent to the function ISNA() returning TRUE.

The following code does NOT work ...

Sub FindNA()For Each Cell In Range("MyRange")If Cell.Value = ISNA() Then
Range("M1").Value = Cell.Address
End IfNextEnd Sub Appreciate if someone could tell me what is wrong with the above and what the VBA equivalent of the ISNA() function is.

Thank you very much.

Hi,

I've searched here for some time looking at how I can find the next date in a column but have not been able to locate what I need, so any assistance is appreciated.

I have a spreadsheet and in R3 I have dates starting at C10 that are 7 days apart ie.. 1/7, 8/7, 15/7, etc..

What I would like is vba code that will look up todays date ie 30/6 and find the next future date which in this case would be 1/7 and then select that column (not including the date, so offset it) from R4:R40.

Regards

David

Hi,
I currently have a functioning spreadsheet with VBA code that takes date from an existing database file somewhere else on the network and inserts it into my sheet as a pivot table. The code that identifies what filename to find is shown below:
Private Sub CommandButton2_Click()
'
'    Month1 = Left(Format(DTPicker1.Value, "mm/dd/yyyy"), 2)
    Month1 = Left(Format(Calendar1.Value, "mm/dd/yyyy"), 2)
'    Year1 = Right(DTPicker1.Value, 4)
'    Month1 = DTPicker1.Month
'    Year1 = DTPicker1.Year
    Year1 = Calendar1.Year
'    DataFile = "" & Year1 & " " & Month1 & " 01 0000 (Wide)"
    DataFile = "" & Year1 & Month1 & "~2"

The filename falls under a directory later identified in the code, but the filename representation is something like this:

2010 09 01 0000 (Wide).DBF

What I'd like to do is modify this code to obtain the previous month's data as well, but I struggle to understand how the code above works exactly. I've tried modifying the last two lines dozens ways with poor results. Specifically only the last line seems to matter when I change it, and I do not understand where the "~2" comes into play, is that some sort of wildcard?

I've tried replacing & Month1 & with & (Month1 - 1 ) & and it comes back and says "cannot find 20108~2", and even if I rename a file 20108 01 0000 (wide). It seems to change the formatting of the file its looking for whenever I introduce the formula, but even modifying the file name and playing around enough with the formula will not return the desired results. Also, I"ve tried placing the entire file name structure in the last line, similar to the 2nd to last line, but it returns with the message that it cannot find the file name "... [Wide]" instead of (Wide), and I cannot seem to find any way around the parenthesis turning into brackets.

I feel like i'm missing some fundamental issue here. Any guidance is appreciated, thanks.

Hi all,

I am very new to the Macro and VBA "underworld" of Excel, and I have a (hopefully) simple procedural formatting task that I believe can be automated; I'm just not sure how.

The short way to explain what I need done is to refer you to the attached worksheet and say that I would like to write code to have excel apply formatting to the "raw" worksheet and make it look like the "formatted" worksheet.

The long way is to provide a detailed explanation of the 3 specific subgoals to this general formatting task:

1a) Find all cells containing the trial name (e.g., "Trial 2 Interruption nil") and 1b) Highlight all cells on the same row yellow

2a) Find all cells signaling the beginning of an interruption (e.g., "Interruption TQ begins"),
2b) Find all cells signaling the end of an interruption (e.g., "Interruption ends"), and
2c) Highlight all cells on the same rows as 2a and 2b and all cells on rows in between 2a and 2b light yellow

3a) Find all cells in column F containing the word "incorrect" and
3b) Highlight those cells red

The purpose of this reformatting task is to make a range of datasets more amenable to coding and analysis. Any help would be greatly appreciated.

I'm still a rookie at this and can readily use any help y'all can provide. I need to have some VBa code to do the following things:

1. Conditionally format a range - cells in "a" should be filled in red with white bold text when value is >20
(value of a is detrmined by subtracting the date in column b from the System date auto entered in c1)

2. Prevent the user from inserting/deleting rows from the Insert menu and from the right click menu

3. Allow users to insert rows that will be properly formatted by using a button (ax:hX) - they should also be able to delete a row (ax:hx).

I realize that this is a rather large request but I am hopeful that there is indeed a generous individual in the group willing to lend me his/her expertise.

Thank you

DummyBook2.xls

Looking for a VBA code:. (all 3 of my conditional formats have been used for different senarios)
Cells "AE4" to "AE2000" contains dates.
I would like adjacent cells "AF4" to "AF2000", to change "Yellow" when "Today's" date falls within a range of 3 to 7 seven days prior to "AE" date, and "Red" if "Today's" date falls within a range of 2 days prior to 365 days after "AE" date .

I want a vba code,that will look for the date in a range A1:I30, then copy and paste it in sheet 2.
The Date format is very bad, its in two rows, one row has month and date and the other row has year, while it can be in any row and column in range A1:I30, but it will be in consecutive rows, like in the below example, the complete date of Mar 31, 2011, the year can be in B3 or A3, but will always be in the next row from the year and day. Also the date format is not fixed. any help on this would be greatly appreciated.
A B C D 1 2 Mar 31 Mar 31 3 2011 2010 4 5

So I'm pretty new to VBA code, and I've tried to help myself as much as possible, but I'm having a hard time debugging this thing and making it work.

Basically, I have two sheets of information: one with a database of contact information, some current, some old. The second sheet has updated information for some of the contacts, and they both use reference ID numbers to identify each contact. I want the code to move down the list of sheet one, searching each time to see if the matching number exists in sheet 2. If it does, it should copy the new information (the cell plus five cells to the right) to sheet 1, replacing what was there.

Here is what I've come up with so far (I know, it's probably pretty bad):


	VB:
	
 Sort() 
     
    Dim count As Variant 
    Dim doppel As Integer 
    Dim newinfo As Variant 
     
    count = 1 
    Range("Census!A2").Activate 
    doppel = ActiveCell.Value 
     
    For count = 1 To 243 
         
        Let newinfo = Sheet2.Cells.Find(What:=doppel, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ 
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
         
        Range("ActiveCell, ActiveCell.Offset(0, 5)").Clear 
        Range(newinfo, newinfo.Offset(0, 5)).Select 
        Selection.Copy 
        ActiveCell.Select 
        Selection.Paste 
        ActiveCell.Offset(1, 0) = 1 
    Next count 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Anything I can do to make this work?

Hi,

I've had a search for similar threads, but can't find one that solves the problem.

I've created a VBA form in which the user enters a date in. When the user clicks a button on the form the date is copied to a cell using the following code.


	VB:
	
 
    Cells(x, y) = Format(frmAdd.txtDate, "dd/mm/yyyy") 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have applied a date format to the column that the dates are being copied to.

The problem I'm having is that if a user enters a date that could be valid as either dd/mm/yyyy or mm/dd/yyyy, for example 05/02/2008, the code assumes that it is in mm/dd/yyyy format and the value copied to the cell is 02/05/2008.

I have found that if I remove the Format function and just use


	VB:
	
Cells(x, y) = frmAdd.txtDate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
,then the date is copied to the cell in the correct format, but if the user enters a date, for example, 5/2/08, it is copied to the cell as 5/2/08 even though the cell format is dd/mm/yyyy not (I would like numbers before 10 to be preceeded with a 0 in the date and the year to be 4 characters long).

I hope I've explained the problem OK, it's getting me a little confused, but I think what I'd ideally like is to be able to change the default date format that VBA uses to dd/mm/yyyy in stead of mm/dd/yyyy so that the code assumes that the user entered date is already in that format.

Help much appreciated.

Thanks.
Joe.

I am attempting to create an automated rotation work schedule by writing a code in VBA that would allow me input the number of days at work and off work which would start on the specified date on sheet 1. Based upon the date input in Sheet 1 D3, this would correspond with Sheet 2 column D to find the correct date listed in column A, then format the specified number of cells from Sheet 1 B3 to the fill color desired. Once the dates and correct number of cells were formated (14 for ex.) the code would then refer to Sheet 1 C3 to determine how many cells to leave non-filled in before filling the next set of cells based on Sheet 1 B3. Next the code would input the corresponding date number from Sheet 2 Column into the first and last filled cell. This code would repeate itself to to the end of the second year.

My hope is to be able to forecast a rotation work schedule based upon the starting date, days at work and days away from work with a few commands. Hopefully someone may an idea on how to help me out here.
Thanks

I would like to know if there is a VBA code to help me sum up the values by date for the following fields (Begin, In, Out, Ending) based on the "Indicator" and "Type" criteria?

The details are in the attached spreadsheet. Thanks in advance for your help!

Hi,
with the help search engine and a miniscule copy/paste job I was able to assemble the following code which is locking cells in a range after data has been entered:


	VB:
	
 Range) 
     
    If Not Intersect(Target, Range("B3:AI28,D29:D33,AM3:AM4")) Is Nothing Then 
         
        If Target.Locked = False Then 
            ActiveSheet.Unprotect "PWD" 
            Target.Locked = True 
            ActiveSheet.Protect "PWD" 
            ThisWorkbook.Save 
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, rows B3:B28 contain a date of employees arrival at work.

What I would like to accomplish next is that even when some cells in range are blank, user without password won't be able to fill them after Date+1 time had passed (they will become locked).

Moreover, in my particular circumstances, another way to accomplish it might be by using the file name, as for every day in a month I create a table with corresponding number. Right now it structured by way of folders and subfolders and the worksheet name contains only day number of the month (e.g. ..2012417.xlsm), but it can be very well be changed (for example into ..2012-04-17.xlsm).

Solution by means of file name might even be preferable, as it would prevent to change retroactively data on days when no employee attended (meaning no date in B3:B28 rows).

Is it as easy as a piece of cake for seasoned Ozgrids? :-)

If it is the case, you have my many thanks in advance!

Hi – I’m really hoping someone can help me with some code to display data in the format I’m looking for. I’m still learning about vba code and I’ve been stuck trying to find a solution, as the ranges in my data will change from week to week.

I have attached my data sample here. I have a range of values in column H that will change in my future data samples, so the code has to be based on the number of unique values in this dynamic range. The values in column A are also unique, they are listed vertically to correspond with each unique value in column H.

MY DESIRED GOAL: I am trying to have the vertical data in columns A, H-J output horizontally based on my dynamic range in row H. I used a formula to calculate the number of unique values in column H, but I still need to figure out how to make the formula dynamic (the formula is located in cell L1).

I created an example of what I want the data to look like in the end, you can see this to the right of column L. I have a grid where the unique values from column H are listed across the top starting in cell O1. Underneath that, there are two horizontal rows for each value in column A, one row is hard-coded titled “weekend” and the second row is hard-coded titled “weekday”. I used two different colors in attempt to better illustrate. The data from columns I-J should output horizontally in this grid, starting in cell O2 in my example. Once the first two rows of data have been populated in the grid, the code needs to know to move down to begin the next loop (i.e. move to O4-O5 to work on the data for the next unique value in column A, which starts in row 22 in my sample). This needs to continue until all rows (i.e. in my sample there are 1121 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid. The biggest problem I have is that this data sample will change from week to week, the only thing constant is that I know what type of data will be in each column, but the number of unique values in column H will always change.

Note: it doesn’t matter to me if the final data is outputted on the same tab like I have it now, or if it’s on a new tab.

CODE: I don’t have much code at this point since I’m not quite sure from what angle to come at this with, I just have a few very basic samples that I have come up with so far. Here is some static code for getting the values for O2-O3 based on the values in I2 and J2; this is just a model to work off of to build out the grid I need, again I need to figure out how to make it dynamic:


	VB:
	
If Range("I2") = "No" Then 
    Range("O2").Value = "No" 
End If 
If Range("I2") = "No" And Range("J2") = "Yes" Then 
    Range("O3").Value = "Yes" 
End If 
If Range("I2") = "No" And Range("J2") = "No" Then 
    Range("O3").Value = "No" 
End If 
 
 'if it's a weekend event
If Range("I2") = "Yes" Then 
    Range("O3").Value = "No" 
End If 
If Range("I2") = "Yes" And Range("J2") = "Yes" Then 
    Range("O2").Value = "Yes" 
End If 
If Range("I2") = "Yes" And Range("J2") = "No" Then 
    Range("O2").Value = "No" 
End If 

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

I also have some code to transpose the number of unique values listed in column H to list them horizontally starting in O1, but my formula in L1 somehow needs to be modified to be dynamic.


	VB:
	
 
 
iNumEvents = Range("L1") + 1 
 
Range(Cells(2, 8), Cells(iNumEvents, 8)).Select 
Selection.Copy 
Range("O1").Select 
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
False, Transpose:=True 
Range("O1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be truly appreciated. This is my first time posting so please forgive anything I may have overlooked.

Hi,

I need to generate dates in a macro using the Datevalue function. However, I want the date string to be generated after detecting the computer's date format (specified in Regional Settings).

The reason for this should be obvious. Datevalue("1/12/2004") could mean a date in January or a date in December, depending on the computer's regional settings.

Hence I need the VBA code to detect the computer's date scheme before generating the date string.

Thanks.

m

Hi Experts,

I try to protect user from deleting the dates. I used the data validation to protect user from changing the date on particular cell but not deleting. Please... show me the VBA code to re-fill the date continue from the above date everytime a user delete the cell. Exampe: Cell A5 has "07/01/03", cell A6 has "07/02/03", cell A7 has "07/03/03" ect.... If I delete cell A6, the system will automatically fill back the date continue from cell A5. Hope this make sense?

Thanks,
Yuki

Hi,

I was wondering whether someone may be able to help me with my problem.

I have data in Sheet 1 that gets entered from a form. It has a range of issues and times in hours that it took to deal with those issues. Also entered from the form is the date the issues occurred and from this I have columns that have the week numbers and month names in.

What I am struggling with is to come up with the VBA code to move the data (the issues and time it took to sort them) into Sheet 2 and arrange them in 2 columns. I would like to do this either with a button or the change event of two combo boxes with the week number and month names in them. Ideally if there are duplicates of the issues then I'd like to sum the hours taken to sort the issues and show in a third column how many occurrences there were of that particular issue and show just the one issue name but even if I could get just the issues and times into 2 columns on Sheet 2 I would be happy, I could sort it manually from there if necessary.

At the moment on the UserForm I have 12 comboboxes that have regular/common issues and 12 textboxes for irregular/one-off issues. On Sheet 1 I currently have columns that count the number of regular issues with CountIf formulas (12 columns) and then on a separate Summary sheet I have SumIf formulas that sum these regular issues for each week and month. The calculations do noticeably slow the spreadsheet down but if I could get the VBA code to work I could do away with the calculations and just run the code when I need to produce the reports which would be so much better.

I have been trawling through different threads on here for quite a few days now trying to find some code that I might be able to adapt but I think my knowledge of VBA is not yet good enough. I have learnt quite a bit though I think, which is good, but I have to admit defeat in getting this right. I have attached an example file to show what I'm trying to achieve.