Hi all I was hoping I could get some help here please.
I have a data input xls file
The purpose of the file is to have employees enter their metrics into a specific row
Once they are done the metric is then to be saved in the Access database.
What I would like help with is(picture the following like a single row with 6 columns
How would I get Metric #1 and Metric #5 to continue as is but to EXCLUDE Metrics #1, Metrics #2,
and Metrics #3,
______________________________________________________________________________
Date | Metric #1 | Metric #2 | Metric #3 | Metric #4 | Metric #5 |
______________________________________________________________________________
This is my code so far
Code
''--------------------------------------------------------------------------------------------------------------------------------------------
'
'Private Sub Worksheet_Change(ByVal Target As Range)
'Dim MDate As String
'Dim MMetric As String
'Dim MTeam As String
'Dim MValue As Double
'Dim MSheet As String
'Dim Dbs As Database
'Dim RST As DAO.Recordset
'Dim DBSPath As String
'Dim DBSName As String
'Dim Msql As String
'
'On Error Resume Next
'
''--------------------------------------------------------------------------------------------------------------------------------------------
'
' If Target.Row > 2 And Target.Row < 64 Then
' MTeam = Worksheets("Info_Input").Cells(2, 2).Value
' ElseIf Target.Row > 74 And Target.Row < 135 Then
' MTeam = Worksheets("Info_Input").Cells(73, 2).Value
' ElseIf Target.Row > 145 And Target.Row < 206 Then
' MTeam = Worksheets("Info_Input").Cells(144, 2).Value
' ElseIf Target.Row > 216 And Target.Row < 277 Then
' MTeam = Worksheets("Info_Input").Cells(215, 2).Value
' ElseIf Target.Row > 287 And Target.Row < 348 Then
' MTeam = Worksheets("Info_Input").Cells(286, 2).Value
' ElseIf Target.Row > 358 And Target.Row < 419 Then
' MTeam = Worksheets("Info_Input").Cells(357, 2).Value
' ElseIf Target.Row > 429 And Target.Row < 490 Then
' MTeam = Worksheets("Info_Input").Cells(428, 2).Value
' ElseIf Target.Row > 500 And Target.Row < 561 Then
' MTeam = Worksheets("Info_Input").Cells(499, 2).Value
' End If
' If UCase(MTeam) = "ALL" Then
' Exit Sub
' End If
' If UCase(MTeam) = "File & TERM ADMIN TEAM" Then
' MTeam = "File AND TERM ADMIN"
' End If
'
' MTeam = Trim(Replace(MTeam, "TEAM", ""))
' MDate = Range("B" & Target.Row)
' MMetric = Cells(2, Target.Column)
' MValue = Target
' MSheet = "Data"
' Dim Mmetric_ID As Double
'
'
' ActiveWorkbook.Save
'
'
'
'
' Msql = " SELECT Metrics.Metric, Reporting_Hierarchy.Level_1, Metrics_X_Reporting_Hierarchy.Metric_ID, Data_Monthly.Date, " _
' & "Data_Monthly.Value " _
' & "FROM ((Metrics_X_Reporting_Hierarchy INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID) " _
' & "INNER JOIN Reporting_Hierarchy ON Metrics_X_Reporting_Hierarchy.Hierarchy_ID = Reporting_Hierarchy.Hierarchy_ID) " _
' & "INNER JOIN Data_Monthly ON Metrics_X_Reporting_Hierarchy.Metric_ID = Data_Monthly.Metric_ID " _
' & "WHERE (((Metrics.Metric)='" & MMetric & "') " _
' & "AND ((Reporting_Hierarchy.Level_1)='" & MTeam & "') " _
' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
'
'
'
'
'
'
'DBSPath = "C:\Documents and Settings\db"
'DBSName = "codb.mdb"
'
'Set Dbs = OpenDatabase(DBSPath & "\" & DBSName)
''
'Set RST = Dbs.OpenRecordset(Msql)
'If Not RST.EOF Then
' 'record exists, find the record in the data_montly table and edit the value of the existing record
'
' If Trim(Target) <> "" Then 'edit the record
' Mmetric_ID = RST!metric_ID
' Set RST = Nothing 'want to reuse the variable - need to clear it out.
' Msql = "SELECT Data_Monthly.Metric_ID, Data_Monthly.Date, Data_Monthly.Value " _
' & "FROM Data_Monthly " _
' & "WHERE (((Data_Monthly.Metric_ID)= " & Mmetric_ID & ") " _
' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
' Set RST = Dbs.OpenRecordset(Msql)
' RST.MoveFirst
' RST.Edit
' RST!Value = MValue
' RST.Update
' Set RST = Nothing
' Else 'delete the record
' Mmetric_ID = RST!metric_ID
' Set RST = Nothing 'want to reuse the variable - need to clear it out.
' Msql = "SELECT Data_Monthly.Metric_ID, Data_Monthly.Date, Data_Monthly.Value " _
' & "FROM Data_Monthly " _
' & "WHERE (((Data_Monthly.Metric_ID)= " & Mmetric_ID & ") " _
' & "AND ((Data_Monthly.Date)='" & MDate & "'));"
' Set RST = Dbs.OpenRecordset(Msql)
' Set RST = Dbs.OpenRecordset(Msql)
' RST.MoveFirst
' RST.Delete
'
' Set RST = Nothing
' End If
'
'Else
' 'record doesn't exist. Find the metric_ID in the CR table - if the metric id is found, insert the record into the data_monthly table
' If Trim(Target) <> "" Then
' Msql = "SELECT Reporting_Hierarchy.Level_1, Metrics.Metric, Metrics_X_Reporting_Hierarchy.Metric_ID " _
' & "FROM (Reporting_Hierarchy INNER JOIN Metrics_X_Reporting_Hierarchy ON Reporting_Hierarchy.Hierarchy_ID = Metrics_X_Reporting_Hierarchy.Hierarchy_ID) " _
' & "INNER JOIN Metrics ON Metrics_X_Reporting_Hierarchy.Metric_Name_ID = Metrics.Metric_Name_ID " _
' & "WHERE (((Reporting_Hierarchy.Level_1)= '" & MTeam & "') " _
' & "AND ((Metrics.Metric)= '" & MMetric & "'));"
'
'
' Set RST = Dbs.OpenRecordset(Msql)
' RST.MoveFirst
' Mmetric_ID = RST!metric_ID
' Set RST = Nothing
' Msql = "Select * from Data_Monthly"
' Set RST = Dbs.OpenRecordset(Msql)
' RST.AddNew
' RST!Date = MDate
' RST!metric_ID = Mmetric_ID
' RST!Value = MValue
' RST!Status = "Active"
' RST.Update
' Set RST = Nothing
' End If
'End If
'
'
'
'
'End Sub