PDA

View Full Version : Need Help: Updating dates monthly



Parker
04-28-2009, 02:55 PM
I am new to all of this, and am just starting to learn my way around VBA. Please help me.

Where I work, I publish a workbook monthly that displays our equipment list and the dates that the last maintenance was performed on. We recently upgraded to a newer version of Maximo that will give me and excel spreadsheet with the equipment numbers and the dates that I need. I just need a simpler way of importing this data into my monthly workbook.

My spreadsheet that I get has the PMNUM and the completed dates in it, I get this information monthly, so I will only be inputting one month at a time. I need marches dates in the march column and lined up with their PMNUM row.

The PMNUM's are organized througout the whole workbook, so this is not limited to just this worksheet.

I cannot post links yet sorry.

:help
David

mdmackillop
04-28-2009, 03:11 PM
Hi David,
Welcome to VBAX.
You can post workbooks using Manage Attachments in the Go Advanced reply section. Remember to remove any sensitive data.
Regards
MD

Parker
04-28-2009, 03:30 PM
workbook added.

Here is the monthly updated information that I receive that has to be imported in.



Thanks MD

mdmackillop
04-28-2009, 03:56 PM
The book is empty and has a link to another file.

Bob Phillips
04-28-2009, 03:56 PM
There is a problem wit that second file, it won't load.

Parker
04-28-2009, 04:04 PM
fixed the attached documents, everything is viewable now

Parker
05-04-2009, 12:36 PM
Any Ideas?

mdmackillop
05-04-2009, 02:06 PM
Option Explicit
Sub GetData()
Dim rng As Range
Dim Col As Long, Rw As Long
Dim txt As String
Dim i As Long, sh As Long
'get column to be filled
Col = 3 + 2 * Month(InputBox("Enter Month") & "/" & Format(Date, "yyyy"))
Set rng = Workbooks("MAXIMO_WorkOrder").Sheets(1).Cells(1, 1).CurrentRegion.Columns(2)
For i = 2 To rng.Cells.Count
For sh = 1 To 1
Rw = 0
On Error Resume Next
Rw = Application.Match(rng.Cells(i), Sheets(sh).Columns(29), 0)
If Rw <> 0 Then
Sheets(sh).Cells(Rw, Col) = rng.Cells(i).Offset(, -1)
Sheets(sh).Cells(Rw, Col).Interior.ColorIndex = 8
Exit For
End If
Next
For sh = 2 To 5
Rw = 0
On Error Resume Next
Rw = Application.Match(rng.Cells(i), Sheets(sh).Columns(29), 0)
If Rw <> 0 Then
Sheets(sh).Cells(Rw, Col) = "DONE"
Sheets(sh).Cells(Rw, Col).Interior.ColorIndex = 8
Exit For
End If
Next
Next
End Sub