Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
I need to write some code to look for missing data. Each month a facility is
supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
Perhaps something like this. Note that this code will produce an error if
the month in B5 is January because there is no column to the left of Column A. Sub Update() Dim TheRng As Range Dim i As Range Dim TheMonthsRng As Range Dim TheMonth As String Dim Col As Long TheMonth = Format(Range("B5").Value, "mmm") Set TheMonthsRng = Range("A9", Cells(9, Columns.Count).End(xlToLeft)) Col = TheMonthsRng.Find(What:=TheMonth, Lookat:=xlWhole).Column Set TheRng = Range(Cells(9, Col), Cells(Rows.Count, Col).End(xlUp)) For Each i In TheRng If i.Value = "" Or i.Value = 0 Then _ i.Value = i.Offset(, -1).Value Next i End Sub "Jeff Gross" wrote in message ... I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
Try this code. I didn't know if the date in B5 was a serial date (formated
as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
What if the data for each month wa not actually next to each other. For
example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
It should make a difference.
Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
The code I gave you searches for the month in row 1 so it doesn't matter in
what column the month is. HTH Otto "Jeff Gross" wrote in message ... What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
Here is my current code but the data is not getting copied from the previous
month when it is zero in the current month. Sub UpdateMissingData() Const MonthlyNameWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Workbooks.Open Filename:=MonthlyNameWbkName Dim TheRng As Range Dim i As Range Dim TheMonthsRng As Range Dim TheMonth As String Dim Col As Long TheMonth = Format(Range("B5").Value, "mmmm") Set TheMonthsRng = Range("A7", Cells(9, Columns.Count).End(xlToLeft)) Col = TheMonthsRng.Find(What:=TheMonth, Lookat:=xlWhole).Column Set TheRng = Range(Cells(9, Col), Cells(Rows.Count, Col).End(xlUp)) For Each i In TheRng If i.Value = "0" Or i.Value = 0 Then _ i.Value = i.Offset(, -1).Value Next i ActiveWorkbook.Save ActiveWorkbook.Close End Sub My current month is in cell B5 and is formatted as date (mmmm). The months are located in row 7. The data begins in row 9. jeff "Otto Moehrbach" wrote: The code I gave you searches for the month in row 1 so it doesn't matter in what column the month is. HTH Otto "Jeff Gross" wrote in message ... What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
Joel - I've tried your code as follows but it does not copy the previous
months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
The problem is simple. MonthName return the full name of the month liked
April. You header row only has three characters. My original code used left to get the first three letters of the month name. The line below needs to be modified. You r code was producing the msgbox saying it couldn't find the month name when I ran the code. mname = Left(MonthName(Month(Range("B5"))), 3) "Jeff Gross" wrote: Joel - I've tried your code as follows but it does not copy the previous months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
I made the change back to your original code and now I see what it is doing.
When the current month cell is 0, it copies the previous column value. The issue is that the months are not adjacent to each other but are separated by 16 columns of other data. How would I go about getting it to copy the value 16 columns before the current month? Jeff "Joel" wrote: The problem is simple. MonthName return the full name of the month liked April. You header row only has three characters. My original code used left to get the first three letters of the month name. The line below needs to be modified. You r code was producing the msgbox saying it couldn't find the month name when I ran the code. mname = Left(MonthName(Month(Range("B5"))), 3) "Jeff Gross" wrote: Joel - I've tried your code as follows but it does not copy the previous months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
I forgot that your columns are not next ot each other. Need to do a search
for the previous month. The code searches Month(Range("B5") - 1 using the first 3 letters of the month name. Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string 'mname = Range("b5") Set C = MonthRange.Find(What:=mname, LookIn:=xlValues) If C Is Nothing Then MsgBox ("Can't find month " & mname) Else If C.Column < 1 Then Lastrow = Cells(Rows.Count, C.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, C.Column) = 0 Then If Month(Range("B5")) 1 Then 'skip january Lastmname = Left(MonthName(Month(Range("B5")) - 1), 3) Set CLast = MonthRange.Find(What:=Lastmname, LookIn:=xlValues) If Not CLast Is Nothing Then Cells(RowCount, C.Column) = Cells(RowCount, CLast.Column) End If End If End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Jeff Gross" wrote: I made the change back to your original code and now I see what it is doing. When the current month cell is 0, it copies the previous column value. The issue is that the months are not adjacent to each other but are separated by 16 columns of other data. How would I go about getting it to copy the value 16 columns before the current month? Jeff "Joel" wrote: The problem is simple. MonthName return the full name of the month liked April. You header row only has three characters. My original code used left to get the first three letters of the month name. The line below needs to be modified. You r code was producing the msgbox saying it couldn't find the month name when I ran the code. mname = Left(MonthName(Month(Range("B5"))), 3) "Jeff Gross" wrote: Joel - I've tried your code as follows but it does not copy the previous months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
That did it. Thanks a lot. That's the second time you helped me with this
spreadsheet project. Take care. I've been trying to locate a good class on VBA for excel but haven't been able to find one yet. Jeff "Joel" wrote: I forgot that your columns are not next ot each other. Need to do a search for the previous month. The code searches Month(Range("B5") - 1 using the first 3 letters of the month name. Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string 'mname = Range("b5") Set C = MonthRange.Find(What:=mname, LookIn:=xlValues) If C Is Nothing Then MsgBox ("Can't find month " & mname) Else If C.Column < 1 Then Lastrow = Cells(Rows.Count, C.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, C.Column) = 0 Then If Month(Range("B5")) 1 Then 'skip january Lastmname = Left(MonthName(Month(Range("B5")) - 1), 3) Set CLast = MonthRange.Find(What:=Lastmname, LookIn:=xlValues) If Not CLast Is Nothing Then Cells(RowCount, C.Column) = Cells(RowCount, CLast.Column) End If End If End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Jeff Gross" wrote: I made the change back to your original code and now I see what it is doing. When the current month cell is 0, it copies the previous column value. The issue is that the months are not adjacent to each other but are separated by 16 columns of other data. How would I go about getting it to copy the value 16 columns before the current month? Jeff "Joel" wrote: The problem is simple. MonthName return the full name of the month liked April. You header row only has three characters. My original code used left to get the first three letters of the month name. The line below needs to be modified. You r code was producing the msgbox saying it couldn't find the month name when I ran the code. mname = Left(MonthName(Month(Range("B5"))), 3) "Jeff Gross" wrote: Joel - I've tried your code as follows but it does not copy the previous months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Data
Your programming skills look good. It just the understanding of algorithms
that takes time to learn. I'm using techniques that I did with Pascal, Fortran, asembly language over 20 years ago and applying these techniques to VBA. The algorithms haven't changed, only the language. "Jeff Gross" wrote: That did it. Thanks a lot. That's the second time you helped me with this spreadsheet project. Take care. I've been trying to locate a good class on VBA for excel but haven't been able to find one yet. Jeff "Joel" wrote: I forgot that your columns are not next ot each other. Need to do a search for the previous month. The code searches Month(Range("B5") - 1 using the first 3 letters of the month name. Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string 'mname = Range("b5") Set C = MonthRange.Find(What:=mname, LookIn:=xlValues) If C Is Nothing Then MsgBox ("Can't find month " & mname) Else If C.Column < 1 Then Lastrow = Cells(Rows.Count, C.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, C.Column) = 0 Then If Month(Range("B5")) 1 Then 'skip january Lastmname = Left(MonthName(Month(Range("B5")) - 1), 3) Set CLast = MonthRange.Find(What:=Lastmname, LookIn:=xlValues) If Not CLast Is Nothing Then Cells(RowCount, C.Column) = Cells(RowCount, CLast.Column) End If End If End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Jeff Gross" wrote: I made the change back to your original code and now I see what it is doing. When the current month cell is 0, it copies the previous column value. The issue is that the months are not adjacent to each other but are separated by 16 columns of other data. How would I go about getting it to copy the value 16 columns before the current month? Jeff "Joel" wrote: The problem is simple. MonthName return the full name of the month liked April. You header row only has three characters. My original code used left to get the first three letters of the month name. The line below needs to be modified. You r code was producing the msgbox saying it couldn't find the month name when I ran the code. mname = Left(MonthName(Month(Range("B5"))), 3) "Jeff Gross" wrote: Joel - I've tried your code as follows but it does not copy the previous months hours when there is a zero in the current month's hours. Here is the code: Sub completedata() Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls" Const MasterSheet = "HS_Monthly" 'contains long names Set MonthlyWbk = ThisWorkbook Workbooks.Open Filename:=MonthlyWbkName Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a7", Cells(7, Lastcol)) 'get month name if it is date format mname = MonthName(Month(Range("B5"))) 'uncomment line if month is string 'mname = Range("b5") Set c = MonthRange.Find(What:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 9 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If ActiveWorkbook.Save ActiveWorkbook.Close End Sub "Joel" wrote: It should make a difference. Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) The above statement finds the last column in row 9 and then sets the entire row 9 to a range call MonthRange. Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) The above line searches the entire row 9 for the month name. "Jeff Gross" wrote: What if the data for each month wa not actually next to each other. For example, January = Column BB February = Column BR March = Column CH April = Column CX May = Column DN June = Column ED July = Column ET August = Column FJ September = Column FZ October = Column GP November = Column HF December = Column HV "Joel" wrote: Try this code. I didn't know if the date in B5 was a serial date (formated as date) or a text string. code is shown as a serial date, but I included a commented line if it is really a string. Sub completedata() Lastcol = Cells(9, Columns.Count).End(xlToLeft).Column Set MonthRange = Range("a9", Cells(9, Lastcol)) 'get month name if it is date format mname = Left(MonthName(Month(Range("B5"))), 3) 'uncomment line if month is string mname = Range("B5") Set c = MonthRange.Find(what:=mname, LookIn:=xlValues) If c Is Nothing Then MsgBox ("Can't find month " & mname) Else If c.Column < 1 Then Lastrow = Cells(Rows.Count, c.Column).End(xlUp).Row For RowCount = 10 To Lastrow If Cells(RowCount, c.Column) = 0 Then Cells(RowCount, c.Column) = Cells(RowCount, c.Column - 1) End If Next RowCount Else MsgBox ("Can't update Column A on worksheet") End If End If End Sub "Jeff Gross" wrote: I need to write some code to look for missing data. Each month a facility is supposed to enter data for that month. I have to download a file which has this monthly data for several facilities compiled into one file. What I need the code to do is look at the date in a cell (B5) and then search the document for the hours associated with that month. If a facility did not enter their data, then I need the code to copy the previous month's hours into the current months hours. For example: B5 = Apr 07 A B C D 9 Jan Feb Mar Apr 10 200 299 104 0 11 400 400 333 233 12 200 300 900 100 13 233 444 555 0 I want to code to change the "0" in column D at row 10 to "104" and D13 from "0" to "555". Thanks. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
connect line to data points with missing data in between | Charts and Charting in Excel | |||
Missing Cloumn data when importing external data | Excel Discussion (Misc queries) | |||
How to determine the cause of missing data after I importing data | Excel Discussion (Misc queries) | |||
How do i plot a data set that has one data point missing | Excel Discussion (Misc queries) | |||
Import External Data is missing some data | Excel Discussion (Misc queries) |