Paste when criteria is met?

[JapanDave]

Ok. This is an exercise for my understanding of VBA. I am a little iffy on the syntax to paste a value in the next cell when a criteria is met?
BTW, I know I could do this via a VLOOOKUP in VBA, but in this instance I don't want to, this purely to learn the syntax for a situation like this

I am using Find in my code to get the value BTW.
Code:

Please Login or Register  to view this content.

[protonLeah]

If Not FVal Is Nothing Then
Worksheets("Sheet1").Cells( ? ).Offset(0, 1).Value = ....

[JapanDave]

Thanks, That takes care of a piece of code that had been bothering me. But, what I am after here is what to put in where the ? mark is. Or, if that syntax is totally wrong. A piece of code that will paste the value of the cell next to the value being looked up in to the corrosponding matched value in col A, and paste that value there. I hope that makes sense?

[nilem]

maybe this

Please Login or Register  to view this content.

[JapanDave]

Thanks for the reply nilem, but it can't be hard coded. I much easier solution already exists, but, I am trying to understand how to go about this in the way I have stated. It is an exercise. http://www.excelforum.com/images/smilies/smile.gif

[dasmith]

Dave,
I am sure I dont understand what you are asking for. I dont understand in your previous response that "it cant be hard coded". Must be misunderstanding what you have in stored in FVal (declared as variant).

This code is actually identical to nilem thus I dont think what you are looking for. This code will find any criteria within a range and paste (insert) a passed in value in the cell next to it.

Please Login or Register  to view this content.

[JapanDave]

Thanks guys for the responses. I have reposted the file. Why I can't have I don't want it hard coded is b/c the same value could be any where in the array. I should have put more of the code in as this is part of a loop. As nilem was so kind to post for me, he has the value to be posted always in Cells(1, 4), but it could be anywhere in col 4 offset(,1).

As I said before this can be easily done with VLOOKUP, but it is an exercise for me. As I just can't seem to get code to work.

Cheers

[tigeravatar]

JapanDave,

Is something like this what you're looking for?

Please Login or Register  to view this content.

[JapanDave]

Hey Tiger,

That is what I was looking for. Than you.

OK, I could pick your brain a bit?

I vaguely understand this line, but how is the intersect interacting will the Variables DCell and rngFound?

Please Login or Register  to view this content.

Now this line I don't understand. What does Trim do? And if the lookup values integers, could I put (DCell.Integer) and then amend the find line to (DCell.Integer) ?

Please Login or Register  to view this content.

I will amend the thread as solved shortly.
Thanks.

[tigeravatar]

I vaguely understand this line, but how is the intersect interacting will the Variables DCell and rngFound?

Please Login or Register  to view this content.

Intersect returns a range that both ranges are in. In this case, the two ranges are usedrange and columns("D").

• The usedrange is simply the range in which the sheet's cells contain data from the first cell containing data in the top-left to the last cell containing data in the bottom-right. In your example workbook, the first column containing data is A, the last column containing data is F. The first row containing data is 1, the last row containing data is 27. So the usedrange is A1:F27.
  
• Columns("D") is the entire column D from row 1 to however many rows are in the sheet.

The Intersect method returns the range that both occupy, so in this case it returns D1:D27.
DCell is the For loop variable that iterates through each cell in that returned range. So basically it says "For every cell in D1:D27"

Now this line I don't understand. What does Trim do? And if the lookup values integers, could I put (DCell.Integer) and then amend the find line to (DCell.Integer) ?

Please Login or Register  to view this content.

Trim removes leading and trailing spaces. This is simply to verify that the cell isn't blank, and doesn't look blank because it contains a space (" "). This check is performed in order to prevent unnecessary finds. If a cell in D1:D27 were blank, we wouldn't want to look for it in column A, so this check would skip that cell.

As for the lookup values being integers, you could use .Value instead of .Text. Because you were looking up dates, I used .Text, though .Value would have worked. There is not a .Integer (trying that would result in an error). You could use CInt(DCell.Value) if you wanted though.

[JapanDave]

Thank Tiger,

That makes the process so much clearer.