If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update a list
I have an excel program where the user goes and inputs data using a list that
I have defined in AT3 through AY3. I then have the user run an update function that will produce all of the data onto another spreadsheet. Originally I had only AT3 through AW3 and I added AX3 and AY3. When I run the update, I cannot get the AY3 to appear on the other page. I am not an expert with VBA but I have tried to update the existing code and I am having no luck. When you use the drop down list to add the fields, it updates the 2nd page with the information. B121 - B126. Unfortunately it will update the first 3 correctly, but will move AY3 into B125, and AX3 into B124. It is suppose to move AY3 to B126, AX3 into B125 and so on. I hope that I haven't confused you and I really don't know how to explain this any further. Is there a way to look at the counter or see if there is a counter in the VBA code that I need to update say from 5 to 6? All of my data for 6 is going into 5 and all of my data for 5 is going into 4 and 4 should be blank. Help me if you can. |
#2
|
|||
|
|||
Update a list
You will need to post your code if you hope to get any help on this.
Pete Mindie wrote: I have an excel program where the user goes and inputs data using a list that I have defined in AT3 through AY3. I then have the user run an update function that will produce all of the data onto another spreadsheet. Originally I had only AT3 through AW3 and I added AX3 and AY3. When I run the update, I cannot get the AY3 to appear on the other page. I am not an expert with VBA but I have tried to update the existing code and I am having no luck. When you use the drop down list to add the fields, it updates the 2nd page with the information. B121 - B126. Unfortunately it will update the first 3 correctly, but will move AY3 into B125, and AX3 into B124. It is suppose to move AY3 to B126, AX3 into B125 and so on. I hope that I haven't confused you and I really don't know how to explain this any further. Is there a way to look at the counter or see if there is a counter in the VBA code that I need to update say from 5 to 6? All of my data for 6 is going into 5 and all of my data for 5 is going into 4 and 4 should be blank. Help me if you can. |
#3
|
|||
|
|||
Update a list
This is where I have the fields defined.
Public Const gsBEV_TEMP_CARTOON_VALUES_COLUMN_START As String = "B" Public Const gnBEV_TEMP_CARTOON_VALUES_ROW_START As Long = 116 Public Const gsBEV_TEMP_CARTOON_VALUES_COLUMN_END As String = "E" Public Const gnBEV_TEMP_CARTOON_VALUES_ROW_END As Long = 132 Public Const gnBEV_FILLER_COUNTERPARTS As Long = 5 Public Const gsBEV_HIDDEN_PROD_START As String = "B" Public Const gnBEV_HIDDEN_BLENDING_KETTLE_1_ROW As Long = 116 Public Const gnBEV_HIDDEN_BLENDING_KETTLE_2_ROW As Long = 117 Public Const gnBEV_HIDDEN_BLENDING_KETTLE_3_ROW As Long = 118 Public Const gnBEV_HIDDEN_BLENDING_KETTLE_4_ROW As Long = 119 Public Const gnBEV_HIDDEN_BLENDING_KETTLE_5_ROW As Long = 120 Public Const gnBEV_HIDDEN_STERILIZER_1_ROW As Long = 121 Public Const gnBEV_HIDDEN_STERILIZER_2_ROW As Long = 122 Public Const gnBEV_HIDDEN_STERILIZER_3_ROW As Long = 123 Public Const gnBEV_HIDDEN_STERILIZER_4_ROW As Long = 124 Public Const gnBEV_HIDDEN_STERILIZER_5_ROW As Long = 125 Public Const gnBEV_HIDDEN_STERILIZER_6_ROW As Long = 126 Public Const gnBEV_HIDDEN_FILLER_A_ROW As Long = 127 Public Const gnBEV_HIDDEN_FILLER_B_ROW As Long = 128 Public Const gnBEV_HIDDEN_FILLER_E_ROW As Long = 129 Public Const gnBEV_HIDDEN_FILLER_H_ROW As Long = 130 Public Const gnBEV_HIDDEN_FILLER_J_ROW As Long = 131 Public Const gnBEV_HIDDEN_FILLER_L_ROW As Long = 132 This is the code that is ran where I run the update at. Public Function UpdateBeverageCartoon(ByVal vsDay As String) As Long Dim sCartoonSheet As String Set moBeverageDict = New Dictionary Set moBKDict = New Dictionary ' name of the cartoon sheet sCartoonSheet = vsDay & gsCARTOON_SHEET_NAME gsCartoonSheet = sCartoonSheet Call ClearBeverageCartoon(gsCartoonSheet, False) Call LoadDataToDictionary(vsDay) ' sort data for blocks Call SortDictionary(moBeverageDict) ' sort data for kettles and sterlizers Call SortDictionary(moBKDict) Call PlaceDataOnBeverageCartoon Set moBeverageDict = Nothing Set moBKDict = Nothing Private Function LoadDataToDictionary(ByVal vsDay As String) As Long Dim oBeverageData As New clsBeverageData Dim nCurrentRow As Long Dim sCode As String Dim nCrew As Long Dim sSize As String With ThisWorkbook.Worksheets(vsDay) nCurrentRow = gnSTARTING_ROW ' get the prodcut code sCode = .Range(gsBEV_PRODUCT_CODE & nCurrentRow).Value ' set day ThisWorkbook.Worksheets(gsCartoonSheet).Range(gsBE V_CARTOON_DAY).Value = _ Format(.Range(gsBEV_DATE_LOCATION).Value, "ddd") ' set date ThisWorkbook.Worksheets(gsCartoonSheet).Range(gsBE V_CARTOON_DATE).Value = _ Format(.Range(gsBEV_DATE_LOCATION).Value, "mm/dd/yy") ' go through sheet while there are product codes While sCode "" Set oBeverageData = New clsBeverageData ' fill in class oBeverageData.sCanCode = .Range(gsBEV_CAN_CODE & nCurrentRow).Value oBeverageData.sCaseCode = .Range(gsBEV_PRODUCT_CODE & nCurrentRow).Value oBeverageData.sDesc = .Range(gsBEV_PRODCUT_DESCRIPTION & nCurrentRow).Value oBeverageData.sFiller = .Range(gsBEV_FILLER & nCurrentRow).Value oBeverageData.sBlender = .Range(gsBEV_BLENDER & nCurrentRow).Value oBeverageData.sSterilizer = .Range(gsBEV_STERLIZER & nCurrentRow).Value oBeverageData.sCaseType = .Range(gsBEV_CASE_TYPE & nCurrentRow).Value oBeverageData.sCaseCount = .Range(gsBEV_CASE_COUNT & nCurrentRow).Value sSize = .Range(gsBEV_BOTTLE_SIZE & nCurrentRow).Value If IsNumeric(sSize) Then sSize = CStr(CInt(sSize)) If InStr(1, sSize, "oz") 1 Then sSize = sSize & " oz" End If oBeverageData.sBottleSize = sSize oBeverageData.sScheduleBatch = .Range(gsBEV_SCHEDULED_BATCH & nCurrentRow).Value oBeverageData.sLapCode = .Range(gsBEV_LAP_CODE & nCurrentRow).Value oBeverageData.sBarCode = .Range(gsBEV_BAR_CODE & nCurrentRow).Value oBeverageData.sStartTime = .Range(gsBEV_START_TIME & nCurrentRow).Value oBeverageData.sEndTime = .Range(gsBEV_END_TIME & nCurrentRow).Value oBeverageData.sFiberCode = .Range(gsBEV_FIBER_CODE & nCurrentRow).Value oBeverageData.sFiberCode2 = .Range(gsBEV_FIBER_CODE2 & nCurrentRow).Value oBeverageData.sCapCode = .Range(gsBEV_CAP_CODE & nCurrentRow).Value oBeverageData.sCapCodeDescription = ..Range(gsBEV_CAP_CODE_DESCRIPTION & nCurrentRow).Value ' check times If ValidateTimes(oBeverageData.sStartTime, oBeverageData.sEndTime, gsCartoonSheet, oBeverageData.sDesc) = SUCCESS Then oBeverageData.sStartTime = ConvertTimeToMiltary(oBeverageData.sStartTime) oBeverageData.sEndTime = ConvertTimeToMiltary(oBeverageData.sEndTime) If Not IsNumeric(.Range(gsBEV_CREW & nCurrentRow).Value) Then nCrew = gnCREW_NOT_ENTERED Else nCrew = .Range(gsBEV_CREW & nCurrentRow).Value End If oBeverageData.nCrew = nCrew ' add to dictionary that will place block data Call AddClassToArrayInDictionary(moBeverageDict, oBeverageData, oBeverageData.nCrew) ' add to dictionary that will place data to fill in cartoon portion Call AddClassToArrayInDictionary(moBKDict, oBeverageData, oBeverageData.sSterilizer) nCurrentRow = nCurrentRow + gnROW_INCREMENT sCode = .Range(gsBEV_PRODUCT_CODE & nCurrentRow).Value End If Wend End With ' XX DEBUG ONLY 'Call DumpDict(moBeverageDict) Set oBeverageData = Nothing End Function Private Function SortDictionary(ByRef roDict As Dictionary) As Long Dim vntItems As Variant Dim vntKeys As Variant Dim nI As Long Dim nJ As Long Dim nK As Long Dim oCurrentBevData As clsBeverageData Dim oTemBevData As clsBeverageData Dim oaCurrentDictItem As Variant vntItems = roDict.Items vntKeys = roDict.Keys ' loop each itme For nI = LBound(vntItems) To UBound(vntItems) oaCurrentDictItem = vntItems(nI) ' lopp each array in dictionary For nJ = LBound(oaCurrentDictItem) To UBound(oaCurrentDictItem) - 1 ' compare loop For nK = nJ + 1 To UBound(oaCurrentDictItem) If nJ nK Then ' swap If oaCurrentDictItem(nJ).ISTimeLessThanMine(oaCurrent DictItem(nK).sStartTime) = True Then Set oTemBevData = oaCurrentDictItem(nJ) Set oaCurrentDictItem(nJ) = oaCurrentDictItem(nK) Set oaCurrentDictItem(nK) = oTemBevData End If End If Next nK Next nJ roDict.Item(vntKeys(nI)) = oaCurrentDictItem Next nI 'Call DumpDict(roDict) End Function Private Function PlaceDataOnBeverageCartoon() Call PlaceBlockDataOnBeverageCartoon Call PlaceKettleInfoOnBeverageCartoon Call RemoveDuplicates End Function Private Function PlaceBlockDataOnBeverageCartoon() Dim vntKeys As Variant Dim vntItems As Variant Dim nI As Long Dim nJ As Long Dim oaFiller As Variant Dim oBevData As clsBeverageData Dim nOffset As Long Dim sColumn As String Dim nItemsPlacedForFiller As String Set moCrewDict = New Dictionary vntKeys = moBeverageDict.Keys vntItems = moBeverageDict.Items ' loop dictioanry For nI = LBound(vntItems) To UBound(vntItems) oaFiller = vntItems(nI) nItemsPlacedForFiller = 0 ' loop array in dictionary, this is each row of data for a filler For nJ = LBound(oaFiller) To UBound(oaFiller) Set oBevData = oaFiller(nJ) If oBevData.nCrew gnCREW_NOT_ENTERED Then ' column to place data in sColumn = GetBlockColumnForFiller(oBevData.sFiller) ' determines row to write data to nOffset = GetRowOffsetForFillerData(oBevData) ' for valid column and row, write the data If sColumn "" And _ nOffset gnOFFSET_NOT_FOUND And _ nOffset gnBEV_DATA_BLOCK_MAX Then With ThisWorkbook.Sheets(gsCartoonSheet) Select Case UCase(oBevData.sFiller) Case "J" .Range(gsBEV_J_BOTTLE_SIZE & gnBEV_BOTTLE_SIZE).Value = oBevData.sBottleSize Case "L" .Range(gsBEV_L_BOTTLE_SIZE & gnBEV_BOTTLE_SIZE).Value = oBevData.sBottleSize End Select .Range(sColumn & gnBEV_START_TIME + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sStartTime .Range(sColumn & gnBEV_CASE_CODE + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sCaseCode .Range(sColumn & gnBEV_LAP_CODE + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sLapCode .Range(sColumn & gnBEV_BAR_CODE + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sBarCode .Range(sColumn & gnBEV_CASE_TYPE + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sCaseType .Range(sColumn & gnBEV_CASE_COUNT + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sCaseCount .Range(sColumn & gnBEV_SCHEDULED_BATCH + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sScheduleBatch .Range(sColumn & gnBEV_STOP_TIME + nOffset * gnBEV_DATA_BLOCK_ROW_INCREMENT).Value = oBevData.sEndTime End With ' fills in description for filler Call PlaceHiddenData(GetFillerRow(oBevData.sFiller), Left(oBevData.sDesc, gsBEV_DESC_LENGTH)) nItemsPlacedForFiller = nItemsPlacedForFiller + 1 End If End If Next nJ Next nI Set moCrewDict = Nothing End Function Private Function PlaceKettleInfoOnBeverageCartoon() Dim vntKeys As Variant Dim vntItems As Variant Dim nI As Long Dim nJ As Long Dim nK As Long Dim oaBK As Variant Dim oBevData As clsBeverageData Dim nItemsPlaced As Long Dim nRow As Long Dim sColumn As String Dim sPreviousFiller As String Dim bDashedLine As Boolean Dim nSecondRow As Long vntKeys = moBKDict.Keys vntItems = moBKDict.Items ' loop dictionary For nI = LBound(vntItems) To UBound(vntItems) oaBK = vntItems(nI) sPreviousFiller = "" bDashedLine = False nItemsPlaced = 0 ' loop array in dictioanry For nJ = LBound(oaBK) To UBound(oaBK) ' only can have 5 items for each blending kettle If nItemsPlaced = 6 Then Exit For Set oBevData = oaBK(nJ) If oBevData.nCrew gnCREW_NOT_ENTERED Then ' fill in sterlizer info nRow = GetSterilzerRow(oBevData.sBlender, nSecondRow) If nRow gnROW_NOT_FOUND Then Call PlaceHiddenData(nRow, Left(oBevData.sDesc, gsBEV_DESC_LENGTH)) If nSecondRow gnROW_NOT_FOUND Then Call PlaceHiddenData(nSecondRow, Left(oBevData.sDesc, gsBEV_DESC_LENGTH)) End If ' fill in blender info, goes to bk, supply tanks nRow = GetKettleRow(oBevData.sBlender, nSecondRow) If nRow gnROW_NOT_FOUND Then Call PlaceHiddenData(nRow, oBevData.sCanCode) If nSecondRow gnROW_NOT_FOUND Then Call PlaceHiddenData(nSecondRow, oBevData.sCanCode) End If ' determines if line from filler supply tank to filler should be dashed ' rule is second product type for that day is dashed If sPreviousFiller oBevData.sFiller And sPreviousFiller "" Then bDashedLine = True End If ' puts in the line Call DrawBevCartoonLine(GetFillerTankFromKettle(oBevDat a.sBlender), oBevData.sFiller, bDashedLine) sPreviousFiller = oBevData.sFiller nItemsPlaced = nItemsPlaced + 1 End If Next nJ Next nI End Function Private Function RemoveDuplicates() Dim nI As Long Dim nJ As Long Dim sColumn As String Dim sPreviouColumn As String Dim sCurrent As String Dim sPrevious As String Dim nOffset As Long Dim nK As Long Dim nNextColumn As String ' loop each row of temp table For nI = gnBEV_TEMP_CARTOON_VALUES_ROW_START To gnBEV_TEMP_CARTOON_VALUES_ROW_END ' loop from item 2 to max For nJ = gnBEV_FILLER_COUNTERPARTS To 2 Step -1 nOffset = nJ - gnBEV_FILLER_COUNTERPARTS sColumn = GenerateColumnForData(nOffset, gsBEV_TEMP_CARTOON_VALUES_COLUMN_END) sPreviouColumn = GenerateColumnForData(nOffset - 1, gsBEV_TEMP_CARTOON_VALUES_COLUMN_END) ' get values sCurrent = ThisWorkbook.Sheets(gsCartoonSheet).Range(sColumn & nI).Value sPrevious = ThisWorkbook.Sheets(gsCartoonSheet).Range(sPreviou Column & nI).Value ' if a match then remove If sCurrent = sPrevious Then ThisWorkbook.Sheets(gsCartoonSheet).Range(sColumn & nI).Value = "" For nK = nJ To gnBEV_FILLER_COUNTERPARTS nOffset = nK - gnBEV_FILLER_COUNTERPARTS sColumn = GenerateColumnForData(nOffset, gsBEV_TEMP_CARTOON_VALUES_COLUMN_END) If sColumn = gsBEV_TEMP_CARTOON_VALUES_COLUMN_END Then ThisWorkbook.Sheets(gsCartoonSheet).Range(sColumn & nI).Value = "" Else nNextColumn = GenerateColumnForData(nOffset + 1, gsBEV_TEMP_CARTOON_VALUES_COLUMN_END) ThisWorkbook.Sheets(gsCartoonSheet).Range(sColumn & nI).Value = ThisWorkbook.Sheets(gsCartoonSheet).Range(nNextCol umn & nI).Value End If Next nK End If Next nJ Next nI End Function The original program only had 4 Sterilizers and I had to add two more. I know that this is a lot of code and it will probably be a little difficult to go through. Any help would be greatly appreciated. "Pete_UK" wrote: You will need to post your code if you hope to get any help on this. Pete Mindie wrote: I have an excel program where the user goes and inputs data using a list that I have defined in AT3 through AY3. I then have the user run an update function that will produce all of the data onto another spreadsheet. Originally I had only AT3 through AW3 and I added AX3 and AY3. When I run the update, I cannot get the AY3 to appear on the other page. I am not an expert with VBA but I have tried to update the existing code and I am having no luck. When you use the drop down list to add the fields, it updates the 2nd page with the information. B121 - B126. Unfortunately it will update the first 3 correctly, but will move AY3 into B125, and AX3 into B124. It is suppose to move AY3 to B126, AX3 into B125 and so on. I hope that I haven't confused you and I really don't know how to explain this any further. Is there a way to look at the counter or see if there is a counter in the VBA code that I need to update say from 5 to 6? All of my data for 6 is going into 5 and all of my data for 5 is going into 4 and 4 should be blank. Help me if you can. |
Thread Tools | |
Display Modes | |
|
|