Solved: updating data from another workbook

[VISHAL120]

Hi

I need some help for the below.

I have two workbooks where one has the main daily data call Critical path and the other one is the consumption file .

The consumption file need to be update with the latest data from the critical path and then later I will used to make further calculation and the record shall be keep.

When an order is completed the order number will be removed from the Critical path but on the consumption file the same order number will be keep as a record and also to the have all the details required.

I have used VBA vlookup to update the consumption file but seems having problem as when the order number is not found in the Critical path it stop.

I have also used another search and update but I can’t figure out why its not updating.

Please find attached the files and also the code.

I have place 2 buttons on the consumption file to better understand. May when testing you will need to change the directory of the file for updating.

Many thanks in advance for the help.

[VISHAL120]

please any help will be very kind.

[p45cal]

When I came to this thread 3 ot 4 days after you started it, 38 people had visited it and the attached file had been downloaded 3 times, but you had no response. This is due to several reasons, amongst which
1. It looks like you want a heck of a lot
2. It's difficult to see what you're trying to do from the code - we have to reverse engineer your code and then try to make it work
3. Your code tries to alter toolbars - which messes up people's own toolbar set-ups - a big no no if you don't warn people

The time that would be required to give you a workable solution is well beyond what people are prepared to give.

So I'm only going to point you in a direction regarding searching for stuff: check out
.Find
which returns a range object if something is found and Nothing if not.
you coluld use the likes of (pseudocode):

Set xxx = RangeToSearch.Find(ThingToFind)
If Not xxx is Nothing then
'do something with the range xxx eg. grab values with xxx.offset(…
Else
'report not found
End if

There are more arguments to .find, look at the help file.

[VISHAL120]

Hi,

Many thanks for the prompt reply,time and advise as i have not think about that.. yes its my fault as i shall have inform before that there is a toolbar set up on that sorry.

and also i shall have put it more clearer for others to udnerstand. i really apologise because i have not think on that. and give a search on what you have advised for sure.

and will also modify the file for a better understanding.many thanks again.

[VISHAL120]

by the way i have used binary search algorithm on one of the code.

which is not working.
here is part code:

For YC_Row_Index = YC_Row_start To YC_Row_End

'Get dates using binary search

Active_POS = Range("Yarn_Consumption_Data!A1").Offset(YC_Row_Index - 1, Pos_Column - 1)
If Active_POS = "" Then GoTo SKIP_ROW

'defining the min and max rows from stock control

Min_Row_INdex_In_Stock = Stock_Row_start
MAx_Row_Index_in_Stock = Stock_Row_end

Do
'finding the mid row values
Mid_Row_Index_in_Stock = WorksheetFunction.Round((Min_Row_INdex_In_Stock + MAx_Row_Index_in_Stock) / 2, 0)


'making case statement

Select Case Range("[Critical_Path_pm_6.xls]Stock!A1").Offset(Mid_Row_Index_in_Stock - 1)

Case Is = Active_POS
POS_Row_INdex_In_Stock = Mid_Row_Index_in_Stock
GoTo Exit_Search_POs_Row_Index_IN_Stock
Case Is < Active_POS
Min_Row_INdex_In_Stock = Mid_Row_Index_in_Stock
Case Is > Active_POS
MAx_Row_Index_in_Stock = Mid_Row_Index_in_Stock
End Select



Loop Until MAx_Row_Index_in_Stock - Min_Row_INdex_In_Stock <= 1

'values to search is either the max or min

If Range("[Critical_path_pm_6.xls]Stock!A1").Offset(Min_Row_INdex_In_Stock - 1) = Active_POS _
Then
POS_Row_INdex_In_Stock = Min_Row_INdex_In_Stock
GoTo Exit_Search_POs_Row_Index_IN_Stock
End If

If Range("[Critical_path_pm_6.xls]Stock!A1").Offset(MAx_Row_Index_in_Stock - 1) = Active_POS _
Then
POS_Row_INdex_In_Stock = MAx_Row_Index_in_Stock
GoTo Exit_Search_POs_Row_Index_IN_Stock
End If
'value to find is not in stock list then

POS_Row_INdex_In_Stock = 0

Exit_Search_POs_Row_Index_IN_Stock:

If POS_Row_INdex_In_Stock = 0 _
Then
'when not found
gge = ""
Cust = ""
Delivery_Date = ""
QTy_Pos = ""
Start_Knit = ""
End_Knit = ""
QTy_Still_To_Rec_AS_Per_Yarn = ""
Cum_ISSK = ""
Cum_RECK = ""
Cum_SNTM = ""
Still_To_ISSk = ""
Still_To_RECK = ""
Still_to_SNTM = ""
Else
'when found

gge = Range("[Critical_Path_PM_6.xls]Stock!A1").Offset(POS_Row_INdex_In_Stock - 1, Stock_Gge_Column - 1)

End If

If gge <> "" Then
Range("Yarn_Consumption_data!A1").Offset(YC_Row_Index - 1, gge_Column - 1) = gge

End If

i just want to know what is wrong with the procedure as it works without any error but it do not update the file.

when i have debug the code i found that the Min_Row_INdex_In_Stock is changing when it is dividing whereas the MAx_Row_Index_in_Stock is staying fixed. whereas i personally think it shaould have been the contrary.

the problem is only with this part of the code.
can you guys please explain me with an example if possible or with the same code how the binary search can be coded.

Many thanks for the reply as for me its only this part which is blocking the file to be updated.

[p45cal]

A binary chop search requires the data to be sorted, and sorted in the right way.
It helps if the data is all text or all numeric, but might get complicated if it's a mixture.
Your search code seems ok in principle.

[VISHAL120]

hi thanks for the quick reply.

but the problem is as already mentioned its changing the Min_row_index instead of the max_row_index. which shall have been the contrary. that is why the data is not being updated. because as its going further by dividing to the last row it do not find anything.


by the way vlookup function which also i have used before, how can i put the code that when i do not find the data itself it shall skip the row completely rather than stopping.

example data to search 1000M, 2000M but 1000M do not exist so it shall skip the row 1000M and go to 2000M.

here the code below:

'Read column indexes from stock sheet on critical path


Stock_Pos_Column = Range("Sotck_pos_column").Column
Stock_Gge_Column = Range("stock_gge_column").Column
stock_cust_column = Range("stock_cust_column").Column
stock_qty_pos_column = Range("stock_qty_pos_column").Column
stock_delivery_date_column = Range("stock_delivery_date_column").Column
stock_qty_still_rec_column = Range("stock_qty_still_rec_column").Column
stock_start_knit_column = Range("stock_start_knit_column").Column
stock_end_knit_column = Range("stock_end_knit_column").Column
stock_cum_issk_column = Range("stock_cum_issk_column").Column
stock_cum_reck_column = Range("stock_cum_reck_column").Column
stock_cum_sntm_column = Range("stock_cum_sntm_column").Column
stock_still_issk_column = Range("stock_still_issk_column").Column
stock_still_reck_column = Range("stock_still_reck_column").Column
stock_still_sntm_column = Range("stock_still_sntm_column").Column
Stock_Header_Row = Range("Stock_Header_row").Row + 1




Yarn_Cons_Row_Start = Consumption_Header_row
Stock_Row_start = Stock_Header_Row

Do While Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Pos_Column - 1) <> ""



'Defining all the paramter to lookup
POS_in_Consumption = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Pos_Column - 1)
gge = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, gge_Column - 1)
Customer = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Cust_Column - 1)
Delivery_Date = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Delivery_Date_Column - 1)
POS_QTy = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, QTy_Pos_Column - 1)
Start_Knit = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, start_knit_col - 1)
End_Knit = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, end_knit_col - 1)
QTy_Still_To_Rec = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, QTy_Still_To_Rec_AS_Per_Yarn_Column - 1)
Cummulative_ISSk = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Cum_ISSK_Column - 1)
Cummulative_RECK = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Cum_RECK_Column - 1)
Cummulative_SNTM = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Cum_SNTM_Column - 1)
Still_To_ISSk = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Still_To_ISSK_Column - 1)
Still_To_RECK = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, Still_To_RECK_Column - 1)
Still_to_SNTM = Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, still_to_sntm_col - 1)

POS_IN_Critical = Range("stock!A1").Offset(Stock_Row_start - 1, Stock_Pos_Column - 1)

' to skip the row if the order number searching do not exsit
If POS_in_Consumption = "" Then
GoTo SKIP_NEXT_ROW
End If

'>>> Searching gge from Crtical Path
Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, gge_Column - 1) _
= WorksheetFunction.VLookup(POS, Range("[Critical_Path_pm_6.xls]Stock!Stock_Data"), 2, False)
'
SKIP_NEXT_ROW:
Yarn_Cons_Row_Start = Yarn_Cons_Row_Start + 1
Stock_Row_start = Stock_Row_start + 1



Loop

[p45cal]

but the problem is as already mentioned its changing the Min_row_index instead of the max_row_index. which shall have been the contrary. that is why the data is not being updated. because as its going further by dividing to the last row it do not find anything.

This would happen if either the data is not sorted, or is not sorted in the right direction (ascending/descending) - you have not confirmed either of these.

by the way vlookup function which also i have used before, how can i put the code that when i do not find the data itself it shall skip the row completely rather than stopping.

Use On Error Resume Next, and cancel it after the line with On Error Goto 0. Within the loop:[vba]xxx = "*^$%£)*&*^" ' any highly unlikely string.
On Error Resume Next
xxx = WorksheetFunction.VLookup(POS, Range("[Critical_Path_pm_6.xls]Stock!Stock_Data"), 2, False)
On Error GoTo 0
If xxx = "*^$%£)*&*^" Then
'it's not found, you may coose to do absolutely nothing, or do what you need, eg. state that it's not found in a cell.
Else
'it has been found
Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, gge_Column - 1) = xxx
End If
[/vba]