Your explanation is difficult to understand...
In general what does the code do?
Did you write the code?
Do you want to create a column?
or
Do something to an existing column?
or
Not do something to an existing column?
or ?
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL
..
..
..
"webels" <(E-Mail Removed)>
wrote in message
news:b9f38d99-9adf-4e66-b6ad-(E-Mail Removed)...
Hi
I have the following code to update a worksheet on a daily basis.
I would like to create a column with a row heading of Reviewed. in
column M.
This is fine but when I rerun the macro below new data overwrites this
column which will be left blank or have a Y for reviewed.
Code as follows
Sub TIPS()
ChDir "M:\Statdata"
Workbooks.OpenText Filename:="M:\Statdata\EDDTIPS.TXT",
Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), 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)),
TrailingMinusNumbers:=True, _
Local:=True '<- this decides date interpretation
Range("A1:M500").Select
Selection.Copy
Workbooks.Open Filename:= _
"G:\Microbiology\Registrars\TIPSICU.xls ", Origin:=xlWindows
Sheets("Main").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("B:B").Select
Set Rng = ActiveSheet
R = 1
N = 1
With Rng
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Do While N <= LastRow
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R,
"#,##0")
End If
V = .Range("B" & R).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to
'vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString
'explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction. _
CountIf(.Columns(1), vbNullString) > 1 Then
.Rows(R).Delete
End If
Else
Next_V = .Range("B" & (R + 1)).Value
If V = Next_V Then
ThisDate = .Range("J" & R).Value
NextDate = .Range("J" & (R + 1)).Value
If ThisDate < NextDate Then
.Rows(R + 1).Delete
''? here
Else
.Rows(R).Delete
End If
Else
R = R + 1
End If
End If
N = N + 1
Loop
End With
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
'header:xlYes refers to the fact that there is a header row
ActiveWorkbook.Save
Windows("TIPSICU.xls").Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows("Macro.xls").Activate
Application.DisplayAlerts = False
Application.Quit
End Sub
Would anyone have any ideas on this one..
Many thanks
Eddie
Similar Threads
