Is there a way to find the 2nd, 3rd... row for a given text string.
1 Bill
2 Ted
3 Sue
4 Bill
I need to know what row the Nth Bill is on.
I want to find the row/position in the array of the Nth incident of Bill, Can this be done with Match and Index
Is there a way to find the 2nd, 3rd... row for a given text string.
1 Bill
2 Ted
3 Sue
4 Bill
I need to know what row the Nth Bill is on.
I want to find the row/position in the array of the Nth incident of Bill, Can this be done with Match and Index
Re: Importing a CSV file with dates in European format
Actually the file is supposed to be is a csv, however, I forgot I was having a problem with that and did change the CSV files extension to Txt for the above code to work.
The problem with .CSV file is that when I open it, it treats the date as American format converts days with the first 2 characters of 01-12 to months. Changing it to TXT allows excel to treat the DMY format as it should be treated then as soon as it opens as written it adjusts it to MDY format in keeping with Local.
Attempting to use a formula like =TEXT(A1,"mm/dd/yyyy") would not work after the file opens because only ~60% of the records end up being text format. the rest are corrupted date serial numbers.
Using your SO macro on the text file imports does not do the text to columns running it on CSV file corrupts the dates.
In short, I want to open a .csv file containing records with European date format DMY and have the date remain a valid serial number date. When I open the CSV, dates like 30/06/2015 is treated as a string and the next record 01/07/2015 is treated as a date but with the wrong month. it is the variable nature of how it is treating these dates that is causing me touble.
Re: Importing a CSV file with dates in European format
I think I found a solution but I don't understand it
the sub called open text sub was recorded using text import where I told it to import the file with the third field in day month year format. the file opens fine. But in trying to understand the limitations of the macro I decided to try and simplify it. So I created a sub called OpenTXTsimplified ()
Sub opentxt()
Workbooks.OpenText Filename:="C:\Users\vahlek\Desktop\EXPORT1438638298826 - Copy.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 4), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), _
Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1)), TrailingMinusNumbers:=True
End Sub
Display More
Sub OpenTextSimplified()
Workbooks.OpenText Filename:="C:\Users\vahlek\Desktop\EXPORT1438638298826 - Copy.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 4)), TrailingMinusNumbers:=True
End Sub
the simplified Macro seems to work seems to work as well but I don't see where it defines Day Month Year or the column to apply DMY format.
Example dataset
Name,Address,Date,Number1,Number2,Number2...
Bill, Oak St, 30/06/2015, 100, 200, 300
Cindy,Ocean Dr, 29/06/2015,200,100,400
Can anyone explain to me what these subs do and why they are able to convert the Euro date in column 3 to American style?
Re: Importing a CSV file with dates in European format
No I have not. How does that work. wouldn't I need to specify a region/local I am not sure what I am saying true to there.
I would need it to read the file as if it was Europe and then via command then tell it to put it in US standard.
Re: Importing a CSV file with dates in European format
I thought this would be an easy one for someone. Perhaps I did not explain my problem.
I have a macro that opens up a CSV file form another software package. This CSV file is used to generate graphs based upon dates. I live in the USA and all my graphs prefer US dating scheme. One of the columns in my csv file is in day month year format is there a way to have excel get the date serial number or simply treat it as text.
I don't particularly want to do any iterative process that requires correcting each date on each line as this will slow my process quite a bit.
I am trying to open a CSV file and the dates are in European format. This causes my macro to have all sorts of problems.
starttime
30/06/2015
1/7/2015
2/7/2015
3/7/2015
4/7/2015
5/7/2015
6/7/2015
7/7/2015
8/7/2015
9/7/2015
10/7/2015
11/7/2015
12/7/2015
13/07/2015
14/07/2015
15/07/2015
16/07/2015
17/07/2015
18/07/2015
19/07/2015
20/07/2015
21/07/2015
22/07/2015
23/07/2015
Display More
Is there a way to open the file and have excel know that this is European format? Then allow me to convert from European to US. I could probably use some form of text conversion but the result of opening the file leaves a mix of dates and strings depending on whether the first number is between 1 and 12 or 13 to 31.
Re: Open file error
So the On Error Goto is not capable of handling this? I need to know so that I don't fall down this path again.
For x = 1 to 10
On Error GoTo Skip_Missing_Data_type
Set WB = Workbooks.Open(RawWBPth(x)) ' Opens file from calculated location Raw Path
' bunch of code
' bunch of code
' bunch of code
' bunch of code
Skip_Missing_Data_type:
Next x
Display More
I am trying to open a workbook, when I attempt to open it I know some of the files will not exist at this point, I need the macro to skip over the next dozen or so steps and go to the next loop. However when I run it and it finds no file it alerts the user rather than going onto the next file.
Public RawWBName As String ' Name of active raw report.
Public KPIReptName As String ' Kpi report name is the name of this workbook.
Public RawWBPth(1 To 5) As String ' Path and file names for all reports to be accessed
Public SheName(1 To 5) As String ' WorkbookSheet names For macro output.
Public AryCount As Integer
Public Frmla(1 To 31) As String
Public FrmCount As Integer
Sub DailyUpdate()
Dim MyName As String
RawWBName = ""
KPIReptName = ""
Erase RawWBPth()
Erase SheName()
AryCount = 0
Erase Frmla()
FrmCount = 0
KPIReptName = ActiveWorkbook.Name
For AryCount = 1 To 5
RawWBPth(AryCount) = Sheets("Update").Cells(7 + AryCount, 2) ' The 7 indicates the row above the paths placed in this array
SheName(AryCount) = Sheets("Update").Cells(7 + AryCount, 3) ' Puts Sheet Range in an array variable.
Next
For FrmCount = 1 To 31
Frmla(FrmCount) = Sheets("Update").Cells(22 + FrmCount, 1)
Next
Display More
on the first loop populating Frmla(frmcount) I get a subscript error. I really do not have a clue why. any suggestions
A little more about the failure. I ran the macro once with it dimensioned wrong I had Frmla (1 to 24) and got a subscript error on loop 25. I corrected dimension Frmla (1 to 31). Ran it again and Then I started getting subscript errors on loop one where I populate Frmla.
I closed the macro, reopened it and edited the dimension of Frmla and then ran it and it runs fine. Am I not clearing Frmla completely or something?
Re: Code jumps out for some reason to previous Subroutine.
oops,
I tried your code it still is a not able to run unless I have the sheet active.
Re: Code jumps out for some reason to previous Subroutine.
Thank you. I am still not sure why the first method did not work but this one does work. I make some work arounds where I activated the sheets and jump around. I will go back to try and incorporate your code as it is much cleaner.
Again thank you.
Re: Code jumps out for some reason to previous Subroutine.
Ok this works sorta, I am not sure why but it appears that the sheet has to be active to function. I thought the with sheets statements should take care of that. I get a 1004 error until I make the sheet magic index sheet active. it fails at the
With .Range(Cells(2, 3), Cells(LastRow, 3))
I have been trying to figure out why my macro jumps to the former subroutine when I perform this line of code.
Sheets("MagicIndex").Selection.Copy
Sheets("magicindex").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
I simply want to make the selected formulas into values.
Sub MagicIndex()
Sheets("Matrix Sheet - nested tabular v").Select ' Redundant
With ActiveSheet.UsedRange
FirstRow = .Row
LastRow = .SpecialCells(11).Row 'selects Last row number
End With
Sheets("Matrix Sheet - nested tabular v").Range(Cells(FirstRow, 1), Cells(LastRow, 1)).Copy
Workbooks(ToolName).Activate
Sheets("magicindex").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
'Sheets("MagicIndex").Activate
Sheets("MagicIndex").Range("A1:A2") = ""
'Range("A1:A2").Select
'Selection = ""
Sheets("magicindex").Range("A:A").RemoveDuplicates Columns:=1, Header:= _
xlNo
Sheets("magicindex").Range("A:A").Copy
'Selection.Copy
'ActiveCell.FormulaR1C1 = "=MID(RC[-2],1,LEN(RC[-2])-1)"
With Sheets("magicindex").UsedRange
FirstRow = .Row
'LastRow = .SpecialCells(11).Row 'selects Last row number
'Sheets.Range("a2").Select
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Sheets("MagicIndex").Range(Cells(2, 3), Cells(LastRow, 3)) = "=MID(RC[-2],1,LEN(RC[-2])-1) & 1"
Sheets("MagicIndex").Range(Cells(1, 3), Cells(LastRow, 3)).Select
Sheets("MagicIndex").Selection.Copy
Sheets("magicindex").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
'**** i am working here*****
Sheets("magicindex").Range("C:C").PasteSpecial
'ActiveSheet.Paste
Sheets("magicindex").Range("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Display More
Re: Change the x axes on all charts.
Ok This is my Newest code. I don't know how to do this if my sheet is inactive but If the macro is launched on my active sheet and I "select" everything I need to, it works. I don't particularly like having to select but this seems to at least function although not as efficiently as I would like.
Re: Change the x axes on all charts.
BTW this is the recorded macro
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
Selection.TickLabels.NumberFormat = "m/d H"
'*** This would be used to change the graphs back ***
'ActiveChart.Axes(xlCategory).Select
'ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
'Selection.TickLabels.NumberFormat = "m/d;@"
I want to automate this to change all Graphs on my sheet. I have tried so many permutations of this that I am beginning to wonder if Excel can't do what i ask!!!
When the user changes they source data from days to hours I need to have the macro make change the axes to reflect the change in some 12 separate graphs.
Below is my entire code thus far. Of course it fails as everything I ever do with objects fails. I Can't get it through my thick head how these things work.
I would appreciate if someone could tell me what I am doing wrong. (well besides trying to program:))
Re: Why cant I directly set Wrap on a range of cells?
Thank you,
It drives me nuts because I can not spot errors like these. I get hung up on these details all the time.
My programs are awesome, My programming sucks.
I would like to simply reference this range of cells and change one formatting attribute.
I get a runtime error 1004 with this???
With Sheets("FlaggedSite Report").Range(Cells(RowV + 1, 2), Cells(RowV + 1, 5))
'.Range(Cells(RowV, 2), Cells(RowV, 6)).WrapText = True
.WrapText = True
End With
This one runs but gives me unpredicted results. This one I think changes the format on my original sheet vs FlaggedSite Report
With Worksheets("FlaggedSite Report")
With Range(Cells(RowV + 1, 2), Cells(RowV + 1, 5))
.WrapText = True
End With
End With
All I want to do is with as small a code as possible make a change to an inactive sheet of my choosing to the wrap format of a range of cells.
Re: how can I make a range of cells the same on an inactive sheet using cells functio
Quote from Luke M;717777
Very intersting. I will play with these that copy resize thing sounds like what I need.
Although I think alketraz's Idea is good as well. Thank your folks for your assist I would be coding right now but my machine is angry with me and excel is not responding after I did a resume this morning after my computers night time sleep.
This does not work
Sheets("flaggedSite report").Cells(lastrow + 1, 1) = Sheets("GS 1").Range("D4:H10")
This Does work
Sheets("flaggedSite report").Cells(lastrow + 1, 1) = Sheets("GS 1").Range("D4")
I would like to silently copy the information in Range D4 to H10 from sheet GS 1 to the last row of Flaggedsite Data.