Microsoft Excel - Would the expression "Range(cells(R,1),cells(r,4)).select" be a valid range object?

Hello Stephen,
Assuming that r and r1 are assigned valid values, it should be.  You might like use ActiveSheet.Range(Cells(r,1),Cells(r1,4)).Select or as appropriate to ensure that you select the cells that you want.
I would use more meaningful names for your variables though.
Regards
Harry

Thank you Harry, I am writing a procedure for looking up the zero values in a range of rows in column 5 and if there is a zero value in one of the cells in the range of rows in column 5 then I want it to copy the range of that row from column 1 through to column 4 to another sheet and then move through the range to do the same thing to any other zero values it finds. At the moment, it is copying the data but it is actually copying it more than once so it is obvious that I have not done the procedure properly, I am still in the learning stages of VBA so I am still getting my head around how some of these finer details work. Once again, many thanks for your prompt response.

Hello Stephen
This might get you started.
Option Explicit

Sub test()

Dim rngTest, rngCell, rngSource, rngDest As Range

Set rngTest = Sheets("Sheet1").Range(Cells(2, 5), Cells(10, 5))

' or could use rngTest = Sheets("Sheet1").Range("E2:E10")

Set rngDest = Sheets("Sheet2").Range("A1:D1")

For Each rngCell In rngTest
    If rngCell.Value = 0 Then
      Set rngSource = Range(rngCell.Offset(0, -4), rngCell.Offset(0, -1))
      rngDest.Value = rngSource.Value
      Set rngDest = rngDest.Offset(1, 0)
    End If
Next

End Sub

Obviously with more detail about data layout and requirements relating to the destination it can be tailored to be more general but if you need more help let me know.
Regards
Harry

   That is very kind of you to do that Harry, much appreciated. In my case, I think it is a matter of getting my head around the structure of the code and practicing it in a number of different scenarios to enforce my understanding of it. Will let you know how I go. Talk to you soon mate.

  Hello Harry,

            Just a question about this "Set" word, what does it do? What would be the difference for example 
            if you had this expression below?

           For r = Cells(Rows.Count, 5).End(xlUp).Row To 8 Step -1

           If I appear to be confusing the two, let me know. Thanks again Harry.
          

Hello Stephen,
You use Set when you are setting a range variable eg Set rngVariable = Range("A1:A10")
With other variables such as numerics and strings you don't need the set eg strString = "abcde"
You can quite validly step through a range by using an incremented variable such as you show but the For Each construct with the Range variable that I used does exactly the same thing with tidier coding.  And, you can make the range variable dynamic to take account of changing data range as well.
Hope this helps.
Harry

    Thank you once again Harry, I will need to read your reply and think about it and let it sink into my head, usually this is       how I absorb things, the penny finally drops, it just takes me a little while that's all. Talk to you soon mate.

Hello Harry,

          Wondering if you could help me with this:

          Sub Macro1()

   
          Dim MySelectedMonth As String
          Sheets("YTD Cashflow").Select
          MySelectedMonth = Range("I2").Value

          Workbooks.Open Filename:="R:\Mang Acct\Cashflow Statements\MySelectedMonth & " " & "Cashflow.xlsm"

           I get the error message: "Expected: end of statement"

          It highlights the " & " just before the "Cashflow.xlsm" I cannot figure out what I am doing wrong, any help 

          would be greatly appreciated.
          

Hi Stephen,
Try
Workbooks.Open Filename:="R:\Mang Acct\Cashflow Statements\MySelectedMonth" & " " & "Cashflow.xlsm"
Regards
Harry

Hello Harry, many thanks for your last assistance, I am trying to decipher the meaning of a wildcard in a formula:

Part of the formula is something like this Find("*")A1, Substitute(a1," ", "*")

Now there are no asterisks in the text so I am wondering what the logic behind substituting a space for an asterisk is, any ideas? Thanks Harry.

Hello Stephen,
I don't think the asterisk is a wild card.  Without having the whole formula (you say it is part), I suspect that the formula is merely intended to replace any asterisks that there are in the string with spaces.  If it is giving you problems from not finding asterisks then you might try wrapping it in a IF(ISERROR(function),result1, result2).
Hope this helps.
Harry