VBA Code needed to identify and retrieve value then put in another cell... HELP!

[NicoleJ1987]

I know it's probably pretty basic, but I'm really a beginner. I need a VBA code that will do the following.

I want to retrieve a value and put it in Cell A5 of Sheet 2, but I must first identify the correct value in Sheet 1 that I need by doing the following.

I want to evaluate/search the an array for a string of (e.g.) the word "Automobile", if this is found in Column A Row 3 of Sheet 1, evaluate the following column of row 3 for the word "Honda" then evaluate the following column of row 3 for the words "Price" and - if all true - retrieve the value "20305.62" from column 4, row 3 and put it in Cell A5 of Sheet 2.

Also, there may be multiple identical lines so I need them to be summed and placed in Cell A5 of Sheet 2. Please help!

 

[venkat1926]

my assumptioons

1.no blank row or column in the main data which is in sheet1
2. the three words automobil in col A,Honda in col. B and "Price" in col c
all in the same row occur only once
3. A5 of sheet 2 is a fixed cell . It has nothing to do the row number in sheet1 in which these three words occur together

my sample data is like this

Excel Workbook
	A	B	C	D
1	hdng1	hdng2	hdng3	hdng4
2	x	x	x	x
3	x	x	x	x
4	x	x	x	x
5	automobile	honda	price	20305.62
Sheet1

if the configuration is substantively different from the sample data you have to modify the macro or post a small extract of your sheet.

try this macro



'

I want to evaluate/search the an array for a string of (e.g.)
'the word "Automobile", if this is found in Column A Row 3 of Sheet 1,
'evaluate the following column of row 3 for the word "Honda"
'then evaluate the following column of row 3 for the words "Price"
'and - if all true - retrieve the value "20305.62"
'from column 4, row 3 and put it in Cell A5 of Sheet 2.

Sub test()
Dim r As Range, filt As Range, ddata As Double, dest As Range
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion
r.AutoFilter field:=1, Criteria1:="Automobile"
r.AutoFilter field:=2, Criteria1:="honda"
r.AutoFilter field:=3, Criteria1:="price"
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
ddata = filt.Areas(1).Columns("D:D")
ActiveSheet.AutoFilterMode = False

With Worksheets("sheet2")
.Range("A5") = ddata
End With

 

[rjperket]

You really should give us an example of what you are working with and why you need it in cell A5. It's hard to help you without more information.