VBA Question for Excel

RebelRebel

I have VBA Excel question I’m hoping someone can help me with.

I have a spreadsheet with two worksheets. One called Data and a second called Computations.

And a very simplified version of what I want to do is as follows: I want to create a macro to check Cell A1 in the Data worksheetis equal to 1 (for example ) and if so insert 1 in cell A1 of the Computations worksheet, otherwise insert 2. So the formual would be very simply =if(Data!A1=1,1,2)

I then want this to continue down the rest of column A until the cells in Column A of the Data worksheet are blank. So I guess I want this to loop until there is no further information to process. And then I want to move on to Column B which would have a different formula.

I can do this by recording a macro and dragging the formula down etc. but I figure there must be a simpler way to code it.

This is just a simple version of what I want to do but if someone can enlighten me on the code I could for this then I thinik I’ll be able to adapt the code to my needs.

Any help greatly appreciated.

TrueDub

What you need to do is define a Range of cells, covering the cells you want to process, then loop through then using a ForEach or similar to do the processing you want.

croo

RebelRebel wrote: »

I can do this by recording a macro and dragging the formula down etc. but I figure there must be a simpler way to code it.

Do that, then examine the macro code created and it will show you exactly how to do it!

Thoie

This may be of some help

intNoOfRows = ActiveSheet.UsedRange.Rows.Count          ' Figures out the last row

Set a variable (intNoOfRows) to find the last used row - you can use that in your looping.

strCopyRange = "B18:B" & intNoOfRows                    ' Makes a string "B18:#" (excludes header)

This is a string that I can use where ever I'd use a range. So in this case if the last used row is 27, for example, this gives me a string "B18:B27". I can then use that variable (strCopyRange) in a range command. For example

Selection.AutoFill Destination:=Range(strCopyRange)     ' This is filling the Range("B18:B<end>").Select down to the last row

Does that help get you started?