VBA post cell data to a worksheet's specific cell based on criteria

[maku]

I'd appreciate any help for this. I want to post the data from one cell on a worksheet to a new column on a worksheet. The tricky part is, the data needs to be posted on a specific row based on 2 criteria (year and quarter).


Example:

Worksheet 1 - Has the criteria and data to be posted

[pre]

Year	Quarter	Data to be posted
2012	3rd	5
Worksheet 2 - Location where data needs to be posted

Year	Quarter	Data to be posted
2011	1st
2011	2nd
2011	3rd
2011	4th
2012	1st
2012	2nd
2012	3rd	5
2012	4th

[/pre]
So, in the above example, we have known criteria of Year = 2012 (A1) and Quarter = 3rd (B1) and the data to be posted (C1). How do I search for the correct row (7) and post the data to the column (C7)?


Thanks! -Maku

 

[kchiba]

Hi Maku,


On Worksheet 2, in column C you can use the SUMIFS function or SUMPRODUCT to fetch the data from sheet 1.

 

[maku]

kchiba...unfortunately that won't work, as sheet 1 is a page that constantly updates and changes based on drop down selections.....kind of like a dashboard. That's why I need a VBA method to add to my existing macro that updates on event change, etc on that page.

 

[kchiba]

Hi Maku,


When the data on Sheet 1 changes, should the existing data on Sheet 2 remain unchanged and change only for the latest data on Sheet 1.


So in your example above if the Data on Sheet 1 changes to


2012 4th 600,


does the data on Sheet 2 remain the same with the new data being aded to


2012 4th 600 on Sheet 2


kanti

 

[kchiba]

Hi Maku,


Assuming that Sheet 1 has the Header in Row 1 and the Data in Row 2 as above.


The table on Sheet 2 has the same Header in Row 1 and the details starting in Row 2. Note the Header and Data must be exactly as it is one Sheet 1 try the following Macro


Sub PostData()


On Error Resume Next


Sheets("Sheet2").Activate

ActiveSheet.ShowAllData

Range("A1:B1").Select

Range(Selection, Selection.End(xlDown)).Select


Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Sheets("Sheet1").Range("A1:B2"), Unique:=False

Range("A60000").Select

Selection.End(xlUp).Select

Selection.Offset(0, 2).Value = Sheets("Sheet1").Range("C2").Value


End Sub

 

[maku]

Hi Kanti. This code copies data from Sheet 1 and overwrites all data on Sheet 2. I only want to post one cell of data from Sheet 1 to Sheet 2, but based on criteria.


What I need is, the code to determine which row the data cell from Sheet 1 (cell C2) in this example should be placed in Sheet 2. In my example above, it would be Row 8, cell (C8) because column A (YEAR) matches 2012 and column B (QUARTER) matches 3rd. That criteria I'm wanting to match is in Sheet 1. Year=A2 Quarter=B2.


Do you know the VBA commands how to match multiple criteria found on Sheet 1 and find that row of criteria on Sheet 2?

 

[SirJB7]

Hi, maku!


Regarding on what should the process do if year/quarter is found (replace value? add value?) or not (add new entry? display error message?), and if first Y/Q value is known and all subsequent Y/Q exists in second sheet, there are many ways to solve it.


Performing a Y/Q search might be done by:

a) using the Application.WorksheetFunction.Index and Application.WorksheetFunction.Match method

b) using a For...Next loop

c) using the Application.Range.Find method


So please confirm previous questions and if there is a preferable mode.


Regards!

 

[maku]

Thanks for responding SirJB7. The answers to your questions are: The year/quarter will always be found as the data that is generated on Sheet 1, (C2) is derived from analyzing other items pulled from Sheet 2 from that corresponding year/quarter. After it is derived on Sheet 1, I want the VBA to post that score (cell C2) to the corresponding year/quarter. If information were updated and the score in Sheet 1 C2 changed, I would want it to replace the cell contents on Sheet 2.


Would it be possible to give me a quick line of which method you think would work best from your ideas of a), b), and c)? Thanks again and Happy Thanksgiving!

 

[SirJB7]

Hi, maku!


Give a look at this file:

https://dl.dropbox.com/u/60558749/VBA%20post%20cell%20data%20to%20a%20worksheet%27s%20specific%20cell%20based%20on%20criteria%20%28for%20maku%20at%20chandoo.org%29.xlsm


This is the code:

-----

[pre]

Option Explicit

Sub UpdateData(iMethod As Integer)
' constants
Const ksInputWS = "Hoja1"
Const ksDataWS = "Hoja2"
Const ksData = "DataTable"
' declarations
Dim rng As Range, iRow As Integer
Dim iYear As Integer, sQuarter As String, iValue As Integer
Dim I As Integer
' start
Set rng = Worksheets(ksDataWS).Range(ksData)
With Worksheets(ksInputWS)
iYear = .Cells(2, 1).Value
sQuarter = .Cells(2, 2).Value
iValue = .Cells(2, 3).Value
End With
' process
With rng
'  row find
For I = 1 To .Rows.Count
If .Cells(I, 1).Value = iYear And .Cells(I, 2).Value = sQuarter Then
iRow = I
Exit For
End If
Next I
If I > .Rows.Count Then iRow = 0
'  row update
Select Case iRow
Case 0
MsgBox "Year: " & iYear & "  Quarter: " & sQuarter & "  doesn't exists. Retry.", _
vbApplicationModal + vbCritical + vbOKOnly, _
"Error"
Case Else
.Cells(iRow, 3).Value = iValue
End Select
End With
' end
Set rng = Nothing
End Sub

[/pre]
-----


I've chosen the 2nd method for the example, but it's only a matter of personal preferences. Just advise if any issue.


Regards!


PS: Happy Thanksgiving day for you too

 

[maku]

Yes! Thank you! Thank you! Thank you! It works exactly as I hoped. I will integrate it into my workbook, but I don't foresee any issues. I am going to research the other two methods just to learn

 

[SirJB7]

Hi, maku!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

 

[excelvba]

Hi I am new to your forum and I have been searching for the past 3 months for s similar solutions and I am happy to find this post. I have tried modifying the vba code to update a range from one sheet to the specific fields in the second sheet but not having any luck. Can someone expand on this post or do I have to do a new post. Basically the code is written with updating 1 row of data at a time. I really would like to update multiple rows at once in the second sheet but not overwriting what is there already.


Any help will be greatly appreciated.

 

[NARAYANK991]

Hi ,


Since you have already gone through this topic , and would like a solution to your problem , as the solution given here does not fit your problem in toto , please start a new topic , and describe exactly what you want done.


If you can upload your sample workbook with both the input data , and the results you expect , you would get a solution faster ; however , please remember that today is a Sunday , and answers are fewer during the weekends.


Narayan

 

[excelvba]

ok. will do thank you so much