Thread: Automatically fill in data table with values from navigator

Hello,

I have a data table of large size in one sheet and i created a simple navigator to pull info from it on another sheet.

The user uses the navigator and may change some values in order to run some simulations. If the values he changed satisfy him, he should be able to save them in the original data table for future reference.

In the table i have a row key that identifies the rows where the value should be changed and the column, is constant.

I do not know how to create a macro that will search all the rows that match the key and change the values of a certain column. I could make a macro that filters the table to match the key and then changes the visible values but im sure there must be a better way.

Can anyone help me with this problem?

Pl attach a mock workbook.

OK So I have managed to make it work...mostly need some help with this last part..

Atualizar_canal should read all the values of one row and copy them to the database following the rules in SearchAndReplace2...the code works for the first value of the first column of the row but it then sends a type mistmatch error before saving the other columns....im guessing my problem is in the FOR loop that saves the column values and then calls the SearchAndReplace2 function

Any ideas?



Sub Atualizar_canal()
'
' Atualizar_canal Macro
Dim Region, SKU
Dim KeyC(11) As String
Dim DChan(11) As Double
Region = Cells(4, 3).Value
SKU = Cells(5, 3).Value

Answer = MsgBox("Deseja alterar a política de canais ?", vbOKCancel, "Política da Região")
If Answer = Cancel Then Exit Sub Else
For CCount = 1 To 11
KeyC(CCount) = Region & Cells(7, CCount + 3) & SKU
DChan(CCount) = Cells(22, CCount + 3).Value
Call SearchAndReplace2(KeyC(CCount), DChan(CCount))
Sheets(2).Cells(22, CCount + 3).Value = 0
Next CCount

End Sub

Sub SearchAndReplace2(ByVal KeyC As String, ByVal DChan As Double)

For lCount = 1 To 65536
If Sheets(3).Cells(lCount, 2).Value = KeyC Then
Sheets(3).Cells(lCount, 31) = Sheets(3).Cells(lCount, 31).Value - DChan
End If
Next lCount

End Sub