can a marco do this.....

[tim963]

For each row in C1:C100 that has anything in it I need to take what’s in column H and paste special valve into column A, so if C1 had something in it, it would take what’s in H1 and paste special valve in A1 then check the other rows, if there is not anything in column C it should go to the next row. I am using excel 97. thanks

 

[Phil Squod]

On 2002-09-08 22:34, GerrySoccer wrote:
Hello Phil or P. Dante:

I am trying to learn VB (in Excel 97) also and I tested Phil's code which works. But can you explain it a bit. Why are there so many "? (e.g. after <>). And the IF statement seems to take RC[7] and put it into the A column, so why the Value statement?

Sorry to bother ye and pardon my ignorance. It is such a short, neat solution.

Thanks,

Gerry

The code does the same as the non-VBA solution posted by ylijohe.

The explanation of each line of code is :-

'Descibe the range to receive data
With [A1:A100]

'Enter in the range the formula =IF(C1="",H1,"")
'The syntax for the formula was obtained by _
turning on the macro recorder, selecting A1:A100, _
typing the formula, and pressing Ctrl+Enter
.FormulaR1C1 = "=IF(RC[2]<>"""",RC[7],"""")"

'Convert A1:A100 to value only
.Value = .Value

'Exit the "With"
End With

Richie said : "Phil - I'm sure your routine was longer than that to start with. A little refining in the editing perhaps?"

Yes, that's right. I got the code by using the macro recorder and originally didn't clean it up as well as was possible - hence the edit.
However, my original code was probably just as efficient as the revised one - just a few more lines of code.

The main thing is that it is generally good practice to use worksheet functions in macros where possible rather than loops.
(But that's not a hard and fast rule - could depend upon what needs to be done.)

Another point to note is that there is quite often an easy non-VBA solution to a user's requirement.
If there is, as in this case, but a macro is still required, then it can be created with the recorder without any knowledge of VBA.
Obviously, therefore, acquiring a sound knowledge of what can be done in Excel without using VBA is very important.

 

[Richie(UK)]

Thanks Phil - I can see I've still got a lot to learn yet. :wink:

 

[GerrySoccer]

Hello Phil:

Thanks for explaining the code and the other tips.

Gerry