I have a database which holds a field for people's credit card numbers. When I try to import them into excel it removes the last number and replaces it with a 0. So if I have credit card number 123456789123456 it will show as 1.23456E+15. Then when i format it as a number is shows as 123456789123450.
I have a userform capturing Credit Card Numbers for Visa, Master, Amex and Diners as part of a payment tracking system and would like to check that the entered Card Number is valid before processing the payment through a third-party website to minimize to number of error / invalid card messages.
I suspect there is some kind of checksum built into the cardnr. Any tips on how to check for the validity would be appreciated, including links to relevant sites.
this question is related to an earlier question of mine: [url]
I have a sheet (1) of data, a1: e200. In another sheet (2) I want to search for values ​​in column B in sheet (1). I want to import the entire row where the value is. The imported row should always go to ex a10:e10 in sheet(2). Because, in sheet (3) I have this letter who gets data from sheet (2) row a10:d10.
How to do this? I can't get it done without copy/paste.,.
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
I need a macro that searches through column F(amount). If it finds a negative amount(a credit note), it should search through column A(client ID), look for the same client and deduct the amount from the oldest invoice of this client. Then it should delete the whole line of the credit note.
If it doesn't find the same client, it should not adjust anything.
If the negative amount is a cancellation(all records with the invoice number 9999999), it should not adjust anything.
If the amount of the oldest invoice is smaller than the credit note, it should deduct down to 0 and deduct the rest of the credit note from the second oldest invoice.
I have attached a sample file : makro credit note.xlsx‎
I have been trying to write a macro but its getting very complicated and im just getting confused. I need to look through a long list of data and see if there is a comparable figure. e.g if there is £350 at branch A for supplier X, i need to know if there is also a credit of £350 at branch A for the same supplier.. The problem is that the comparable figure could be above or below the original figure. Then i will delete both these lines from the list. I have tried to do this but have got cofused by so many if and else formulas.
where the first term refers to cell immediately above. In other words, this formula takes the prior balance, adds a credit if any, subtracts a debit, if any, and gives a new balance.
The formula worked on the cell I put it in. It will not work when I copy and pasted it into the cell below the first one. Error I get is circular reference.
I want to catch an error and reset the entire code to the very beginning and skip that entire entry. When I use "Next fieldSheetName" I get "Next without For," error 1004. Searches tell me I have an open block somewhere, but that's not true. Removing that statement (and having the loop iterate as normal) has no error at all.
Dim employeeName As String Dim fieldMax, x, y As Byte ' Counters mostly Dim workedHours, fieldSheetName As Integer
fieldMax = 204 ' Row number to stop on in the field time sheet row = 4 ' Row specification for field time sheet. Begin at row 4 to ignore headers ' and start on the first name. This should not be changed! Col = 3 ' Start at column 3 then increase by one to start going to next time entry
' RESET HERE! For fieldSheetName = 4 To fieldMax Step 8 ' This is our MAIN loop. It iterates from 0 to fieldMax, which is 204...........
I have 2 reports starting col A to W. I want a macro code for the below.
1st Report
1. 1st sort the entire report with col O leaving the headings. 2. 2nd delete the rows if where value of O is "Credit" 3. After the above step sort the entire report again with col H
2nd Report i hvae is also from Col A to AB. I want a macro code for below.
1. 1st sort the entire report with col O leaving the headings. 2. 2nd delete the rows if where value of O is "Credit" 3. 3rd delete the rows if where value of AB os "DEL" 4. After the above step sort the entire report again with col H
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
I have a scorecard created in excel 2007. All my end users are using excel 2002. Now I want to build a scorecard in Excel 2002 like what we have in excel 2007. Is it possible.
I want to display the traffic signals and 5 level indicators (arrows), based on conditional formatting.
This is out of box in excel 2007. But how do I achieve this in excel 2002? Is there a simple method?
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
Sub HideDatedSheets() Dim wb As Workbook Dim ws As Worksheet Dim dDate As Date
[Code]...
So what im trying to do is keep the Union* sheets shown as well as the Report* sheets shown. But i believe i have the code wrong because they are not showing up when the work book opens...
Trim(ws.Name) = "Union*" Or _ Trim(ws.Name) = "Report*" Or _
How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.
I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.
I want to do it without having to enter the colon.
I'm trying to autofill a series of rows (that are blank) with data from an above row. I want to autofill the row in its entirety, not just filling in blank cells.
For instance (assuming comma is a new column). Colors listed are just a data example. Space between commas indicates a blank cell:
So rather than it just completing the task in one desired cell, it would complete the task over the entire spreadsheet. Data spread can be as far as row 500 and column BY, so you can see how a copy + paste or a drag would get monotonous.
I'm trying to use the COUNTIFS function & have run into trouble because the 2nd criteria is a date. Typically I would just set the range & then put "01/01/2014" if I was looking for all of the January 1, 2014 cells. However in this spreadsheet my source data contains the date with the time. (01/01/2014 11:27:39 AM). Becasue the time is included in the cell, the function returns no results when I set teh date.
I tried to use a wild card "01/01/2014*" which I thought would bypass the time but it still retuns no results. Is there a way to use a windcard with a date cell?
I have a workbook containing 2 sheets. In the first sheet I have a list of values, let's say that the list contains names:
Worksheet1 Column A Peter Paul
In another worksheet I have another column, also containing names:
Worksheet2 Column A James Harrow Paul Givens
I wish to perform a vlookup in worksheet2 but cannot use wildcards in the table array :
Vlookup ("*"&ColumnA&"*";Table1[Table or table and column];1;false). What I want should look like the following: ("*"&ColumnA&"*";"*"&Table1[Table or table and column]&"*";1;false)
In this problem, it is not appropriate to query back to front; i.e. vlookup worksheet1 instead of vlookup in worksheet 2.
I have seen an interesting post that uses macros (on this site, I think) but was not able to implement it.
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.