I have a question regarding Do While loops. I'm trying to write a do while loop to search through an array for a particular value and return the row number. This value is in the first column of the array and there are 211 of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times, then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
I have a spreadsheet which needs to display currency in the correct format dependant upon whether a contract is in English or German Law. Excel defaults currency format as €310,000.00 however in German this needs to be €310.000,00 i.e. with the decimal point and comma in opposite positions. Does anyone know what expression I can use to format it as the latter?
I am trying to date and time stamp a certain cell. This cell is going to be compared to another cell in another sheet (also date and time) so that I can track deadlines being met.
Even with the number format, the cell is not stamped as a number. As a result I get #value errors when I take the difference between the stamped cell and the cell with the preset deadlines.
I have been trying to find an easier way to make sure that the email address that I have been given in a excel workbook are in the proper format before I try to use them. I have tried doing a google search on this subject but have not found the correct solution. I want to enter a function in column L that would use a regular expression for the email format and compare it to the email address in column J. If the results are true place a value of 1 in column K. If false place a value of 0 in column K.
When I import data , the dates appear as follows in Col G sheets "Imported Data"
If I select Col G and use Text to columns and select mdy, it gives me the dates in the correct format. However, If I use the macro recorder to do this and then run the macro, it does not work
I have attached the dates after using text to Columns wjhich is the correct format.
Imported Assets  G201/07/2006301/02/2007401/02/2007501/11/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921301/02/19931401/05/19931501/06/19931601/08/19931701/01/19941801/09/1995
We have a system that normally dumps our data in the format of column K, with all of the data in one column and the same spacing you see below in red. The problem is when the system is slow, we have to manually dump it, and when we manually dump it, it comes out spread out from column A to column I. For the macro to work without bugging out on me, it has to be in the same format as column K, with exactly the same spacing.
I have tried text to columns while importing, amongst other things, but have not had any luck. So in a nutshell I need to be able to make column A thru I, look just like column K with exactly the same spacing. The data dumps are different every day.
I have a strange feeling I will not get a reply on this question, because it is so strange, or that I failed to articulate it correctly....
I have a querytable running on my sql database. In the query I have a trimleft function because the numbers are stored with several proceeding blank spaces. When I execute the query through my macro, everything works fine, except I can't average or sum the results. They can be multiplied, divided, etc, but not averaged or summed (they just show as zero on summing, which makes the average DIV/0). The really strange part is that if I select a number and go to the formula bar and hit delete at the beginning of the number (which actually deletes nothing), the number is suddenly capable of actually performing as a number. I assume this has something to do with how the querytable is returning my results, but does anyone have an idea for a workaround for this issue?
I have several sources of information for client files. the number format includes a reference number and a month, eg 123/11, 456/02. In some cases, the numbers may be presented including the company initials, eg ABC/00123/11. I want to make sure all numbers on all sources are the same as I am using Vlookup and this reference no to copy information from one source to another. Sometimes when I change the format Excel sees this as a date, eg 11/11 is 11-Nov, and in some cases Excel sees a fraction, eg 123/11 is 123 11ths or 123 divided by 11.
I can easily take out the reference no with a find and replace FIND ABC/ replace with . But this often results in a number with leading zeroes, eg ABC/0123/06 will leave 0123/06.
I have a spreadsheet with five cells (L3 - L7) that have dates entered into them in the format of 9/9/2013, 9/10/2013, 10/20/2013, 10/21/2013, 10/22/2013. In another cell, cell n2, I have the formula:
In cell n2 I get: 41526415274156741568481569 This is the excel number for the above dates, but I need it to look like:
Sep. 9 - 10, 2013, Oct. 20 - 22, 2013
I also need it to be able to isolate single dates for example if the date in cell l5 was 10/5/2013 instead of 10/20/2013 then I would want n2 to look like Sep. 9 - 10, 2013, Oct. 5, 2013, Oct. 21 - 22, 2013. but my formula currently only changes that number in the string from 41567 to 41552.
The reason I need this is because this information is part of a identifier and also maybe used in reports, the people reading the reports won't know this long number is actually dates and won't be able to read them if they did.
Also the cells L3 - L7 data depends on cell l2, 1 = one date entered into cell l3, 2 = two dates, one in cell l3, the next in cell l4, and so on for up to five total days that may or may not be consecutive. Also, it seems to work as is, but only for one day.
I have the following code to ensure user inputs date in correct format, but on test it doesn't capture if they enter 11.12.08 (Which we have had people do before), if that is entered the textbox returns 30/12/99, is there a way I can prevent this?
Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'Checks Start Date is in correct date format If IsDate(txtStartDate.Value) Then txtStartDate.Value = Format(DateValue(txtStartDate.Value), "dd/mm/yy") Else MsgBox "Please enter a valid date format." Cancel = True End If End Sub
I have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
Problem: Placing a With/End With for FormatConditions in a For/Next loop isn't applying the conditional formatting.
Information: - I have a stats section that has months as the column headers with totals at the end (used as a VLOOKUP table) - Each column needs the conditional formatting separately - The columns are static, but the rows are variable - Taking it out of the loop and coding each loop individually works perfectly
The problem code:
finalRowTable = Cells(Rows.Count, 12).End(xlUp).Row For i = 13 To 38 With Range(i & "11:" & i & finalRowTable) .FormatConditions.Delete .FormatConditions.AddTop10 .FormatConditions(1).TopBottom = xlTop10Top .FormatConditions(1).Rank = 1 .FormatConditions(1).Percent = False .FormatConditions(1).Interior.Color = 5296274 End With Next i
I checked after running it and it doesn't show up in the Manage Rules Conditional Formatting
Code that works without loop: Code: With Range("N11:N" & finalRowTable) .FormatConditions.Delete .FormatConditions.AddTop10 .FormatConditions(1).TopBottom = xlTop10Top .FormatConditions(1).Rank = 1 .FormatConditions(1).Percent = False .FormatConditions(1).Interior.Color = 5296274 End With
I've been playing with excel VBA for a few months now, so it's probably something simple.
i have 3 columns and hundreds of rows.... i first applied conditional format to the first row (A1, B1, C1) only. no problems so far as A1 has value '0' which is minimum and B1 is the highest value '200' and C1 is variable value between 200' and 0'. Im using icon sets in this one.... now i want the below rows to have the same conditional format.... how do i do that because when i select them all rows and columns and try to apply conditioanl formatting the outcome is not the same as i got previously in the first row...it somehow combines all the values rather working only on the individual row. i hope u get wat im tryin to say....
i come across a solution in my mind to give a loop in vba to do the conditional formatting each row seperately untill empty cell of column no. 4.... but doing this will obviously eat lot of memory in excel cause of each row will have a seperate conditional format.....
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good. I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select for n = 1 to 4 if activecell = "A" then Label1.BackColor = RGB(0, 0, 0) else if activecell = "B" then Label1.BackColor = RGB(0, 0, 255) else if activecell = "C" then Label1.BackColor = RGB(0, 255, 0) end if activecell.offset(0,1).select next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on. secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
i'm trying to convert a column (P) from date dd-mmm-yy, subtract 5 days, and paste value as YYMM using a macro. I've borrowed bits from other macros and producted this but it's not working (and i've played around with it to the point it doesn't make sense anymore!)
Option Explicit
Sub ADD_REPORT_DATE() Dim LR As Long, i As Long Dim tempValue1 Dim tempValue2 As Date
Worksheets("Report").Select LR = Range("P" & Rows.Count).End(xlUp).Row For i = LR To 1 Step -1
Set ActiveCell.Value = Text(ActiveCell.Value - 5, "YYMM")
I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:
Code: Sub UpdateTextAllStateCharts() 'This macro loops through worksheets in a list and performs the assigned task Dim sheet_name As Range For Each sheet_name In Sheets("WS").Range("C:C") If sheet_name.Value = "" Then
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format() Dim VarFileName As String Dim VarPath As String Dim VarSavein As String Dim wsheet As Worksheet VarSavein = Sheets("sheet1").Range("C2").Value VarFileName = Sheets("sheet1").Range("A2").Value VarPath = Sheets("sheet1").Range("B2").Value Workbooks.Open VarPath & VarFileName For Each wsheet In ActiveWorkbook.Worksheets Sheets(wsheet.Name).Select Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ := Array(1, 2), TrailingMinusNumbers:=True..........................................
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4() Dim r As Range Set r = Range("A1:A6") With Application.FindFormat.Font .Bold = True .Italic = True .Size = 16 End With r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate End Sub
With Sheets("regrade pharm_standalone") For Each r In .Range("standaloneTerritory") If r.Value = "X101" Then r.EntireRow.Copy Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues End If Next r End With ------------------- I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro() Dim lastRow As Long Dim i As Long
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range Dim TotalRows As Long TotalRows = Cells(Rows.Count, 1).End(xlUp).Row For Each CELL In Range("C1", "C" & TotalRows) CELL.Select 'Code here to delete a row based on criteria Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop() ' ' Loop through an indexed number of worksheets; _ ' & this ensures that the worksheet range is dynamic _ ' and is able to adjust when new sheets are added/removed, etc. ' 'Dim ws As Worksheet Dim i As Long Dim ws As Worksheet
