Posts by MikeCheck
-
-
Re: Changing X,y Scatterplot X-axis Time Scale
Double check the years on those dates. Make sure that at January the year has incremented.
-
Re: Vlookup With Partial Match Only
Try using MATCH instead. Something like this:
=INDEX('[Project Database_Updated 04-30-08.xls]Sheet1'!$A$1:$E$1007, MATCH(A4,'[Project Database_Updated 04-30-08.xls]Sheet1'!$A$1:$A$1007),2)
-
Re: Speeding Up A Vba Macro
I find that deleting the rows independently takes a long time. I usually use an empty column, and have the code write an "X" in that column next to the rows that are going to be deleted. Then, after it's done processing all rows, the code will then sort the cells based on the column with the X's in them, and delete the entire block of rows.
-
Re: Copy Data To 1,000,000 Rows
Step 1: Copy the cell with the formula
Step 2: Select the first blank cell you would like to copy the formula to.
Step 3: Press Ctrl+Shift+(down arrow)
Step 4: Paste -
-
Re: Finding The Most Occurrences Of A Value Across Multiple Rows
COUNTIF is a built-in worksheet function. You don't need VBA.
-
Re: Find And Replace
Code
Display MoreSub FindWord() Dim FindString As String Dim FindCell As Range FindString = InputBox("Enter the word:") If FindString = "" Then Exit Sub For Each FindCell In Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A65536").End(xlUp)).Cells If InStr(FindCell, FindString) > 0 Then FindCell.Offset(, 1) = FindString Next FindCell End Sub
-
Re: Format Cell Value From Formula To General
Try this for your formula:
=VALUE(MID(A12, 18,5))
edit: shg beat me to it.
-
-
-
-
Re: Sorting List By Multiple Columns
Go into "Go Advanced". Then click on the Paperclip next to the smiley face. That will allow you to attach a file.
-
Re: Append To & Replace Cell Data By Condition
Quote from Craig OttleyYes and mainly no, reason being you can do this in one step not 5.
I'd do this...
- Step through each of the cells in your desired column.
- If the value meets the regular expression then replace the value of the cell with new value.
- If the value doesn't meet the expression then do nothing or something else...
You'll cut down on processing time doing it that way...
HTH
Craig
Actually, I believe inserting a column, writing an equation to that entire range, then copy paste and delete the column is faster that stepping through each cell. I could be wrong though. I may have to set up my own test and time it.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]
Quote from MikeCheckActually, I believe inserting a column, writing an equation to that entire range, then copy paste and delete the column is faster that stepping through each cell. I could be wrong though. I may have to set up my own test and time it.
Well, I did the test on 65000 records. Craig's suggestion (Test2) was 2 seconds faster than the worksheet formula method (Test1). For reference here are the two tests I ran:
Code
Display MoreSub Test1() Dim StartTime As Double Dim EndTime As Double StartTime = Time ActiveSheet.Range("A1").EntireColumn.Insert Intersect(ActiveSheet.Range("A1").EntireColumn, ActiveSheet.UsedRange.EntireRow) = "=""My"" & LEFT(RC[1],3) & IF(MID(RC[1],4,2)=""00"","""",""-"" & RIGHT(RC[1],LEN(RC[1])-3))" Intersect(ActiveSheet.Range("A1").EntireColumn, ActiveSheet.UsedRange.EntireRow).Copy ActiveSheet.Range("B1").PasteSpecial xlPasteValues ActiveSheet.Range("A1").EntireColumn.Delete EndTime = Time MsgBox (EndTime - StartTime) * 24 * 3600 & " seconds" End Sub Sub Test2() Dim StartTime As Double Dim EndTime As Double Dim TestCell As Range StartTime = Time For Each TestCell In Intersect(ActiveSheet.Range("A1").EntireColumn, ActiveSheet.UsedRange.EntireRow) If Mid(TestCell, 4, 2) = "00" Then TestCell = "My" & Left(TestCell, 3) Else TestCell = "My" & Left(TestCell, 3) & "-" & Right(TestCell, Len(TestCell) - 3) End If Next TestCell EndTime = Time MsgBox (EndTime - StartTime) * 24 * 3600 & " seconds" End Sub
To me the difference is negligible. Using one or the other should be based on personal preference rather than efficiency.
-
Re: Sorting List By Multiple Columns
Can you post an example?
-
Re: Filtering By Vba
If this is an exam you're taking, I think you should really figure it out on your own. It's supposed to test you're own knowledge and skills.
If not, then here's what my solution would be:
=IF(ISNA((MATCH(LEFT(D1,4),$A$1:$A$4,0))),OFFSET($A$1,MATCH(LEFT(D1,3),$A$1:$A$4,0)-1,1),OFFSET($A$1,MATCH(LEFT(D1,4),$A$1:$A$4,0)-1,1)) -
Re: Append To & Replace Cell Data By Condition
Quote from LucianpThanks Mike,
No. You'll need to put the equation in another column, then do a copy...pastespecial values over the original data.
So I would create a macro to:
[INDENT]insert a column,
run the formula,
copy...pastespecial to the original column
delete the inserted column[/INDENT]Is that correct?
Lucianp
Yes, that is exactly what I would do. Or, if you don't want a macro, you could do it manually.
-
Re: Append To & Replace Cell Data By Condition
Quote from LucianpMike & Craig,
thank you both very much for you help. Mike's formula did it. I just needed to make minor tweaks.
I copy/pasted the formula and it wasn't quite right so I manually copied it in so that I could get a grasp of what the formula was doing. After messing around with the numbers a little bit, I got it right.="My" & LEFT(A1,3) & IF(MID(A1,4,2)="00","","-" & RIGHT(A1,LEN(A1)-3)) Mikes formula
="My" & LEFT(A1,[COLOR="Red"]4[/COLOR]) & IF(MID(A1,[COLOR="Red"]5[/COLOR],2)="00","","-" & RIGHT(A1,LEN(A4)-[COLOR="red"]4[/COLOR])) My formulaI tried to run it in the same cell and get an error saying that I am creating a loop. Is it possible to run a formula like this to REPLACE in the same cell?
Again, many thanks for your help.
Regards
LucianNo. You'll need to put the equation in another column, then do a copy...pastespecial values over the original data.
-
Re: Read International Date In Correct Format
Assuming that the General format cells are showing the date as text in the form of "dd/mm/yyyy", try this formula:
=IF(ISTEXT(A1),DATE(RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1)),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),IF(ISTEXT(A1),LEFT(A1,FIND("/",A1)-1),FALSE)),A1)
-
Re: Append And Replace Data
Here's a worksheet formula:
="My" & LEFT(A1,3) & IF(MID(A1,4,2)="00","","-" & RIGHT(A1,LEN(A1)-3))