Help with VBA

[Mike7777]

I am working on a spread sheet that in one specific cell(we'll say Sheet1 A1) I need it to display another cell on another page(Sheet2 B36). Thats easy enough, but then at each calculation I need the cell above the original cell that was being displayed to be displayed (so now its displaying Sheet2 B35 in Sheet1 A1) and at each calculation after go up one cell each time.
Is this possible? I've tried googling this and I can't find anything.
Thanks for any help
Much Appreciated
Mike

edit, There will be no formula in Sheet1 A1 I just need it to display the value of Sheet 2 then up one per calculation.

 

[offthelip]

Yes that would work and ofcourse you can move the second currow ( currow2) in a completely different way, ie. you could increment by two rows each time instead of decrementing it.
Another alternative, if the difference in positon between your two variables is fixed i.e always six rows above the first you can calculate the second one. for example say you wanted to fill columns C and D of sheet1 from columns D and E but six rows above where you are picking columns B and C, you just add this line of code below the first one.

Range(Cells(1, 3), Cells(1, 4)) = Worksheets("Sheet2").Range(Cells(currow-6, 4), Cells(currow-6, 5))

 

[Mike7777]

I'm trying to understand?lol Its giving me a compile Error. I've tried it a couple different ways and I've yet to get it write.
This is where I'm at now. What all do I have wrong?

Sub NETWORK()'
' NETWORK Macro
'








'
Dim k As Integer
k = 0




Currow = 5000
Currow2 = 4999
Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)
For i = 1 To 10
Calculate
If Currow > 2 Then
 Currow = Currow - 1
 If Currow2 > 2 Then
 Currow2 = Currow2 - 1
End If
Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)


Range("V8:AC33").Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Cells(1, 1) = 1 Then k = k + 1








Next i




Cells(2, 1) = k


End Sub

line 4999 shows up where its suppose to but it's not moving? At least at first it did, now it gives the compile error

 

[offthelip]

you are missing an endif;

Sub NETWORK() '
' NETWORK Macro
'
'
Dim k As Integer
k = 0
Currow = 5000
Currow2 = 4999
Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)
For i = 1 To 10
Calculate
If Currow > 2 Then
 Currow = Currow - 1
 If Currow2 > 2 Then
 Currow2 = Currow2 - 1
End If
[COLOR=#ff0000]End If[/COLOR]
Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)




Range("V8:AC33").Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Cells(1, 1) = 1 Then k = k + 1
Next i
Cells(2, 1) = k
End Sub

 

[offthelip]

Another comment: previously you were copying columns B and C from sheet2 to columns A and B on the active sheet. Your most recent code is copying two rows from column C to B3 and D3 on the active sheet ,
Is this correct

 

[Mike7777]

Thank You offthelip very much for your help. It works very well now!
Thank you

 

[MARK858]

I don't see any reason why you can't do away with the End If's altogether (as well as the Select/Selection).

Sub NETWORK()                                    '
    ' NETWORK Macro
    '
    '
    Dim k As Integer
    k = 0
    Currow = 5000
    Currow2 = 4999
    Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
    Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)
    For i = 1 To 10
        Calculate

        If Currow > 2 Then Currow = Currow - 1
        If Currow2 > 2 Then Currow2 = Currow2 - 1


        Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
        Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)


        Range("V8:AC33").Copy
        Range("B8").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        If Cells(1, 1) = 1 Then k = k + 1
    Next i
    Cells(2, 1) = k
End Sub

or

Sub NETWORK()                                    '
    ' NETWORK Macro
    '
    '
    Dim k As Integer
    k = 0
    Currow = 5000
    Currow2 = 4999
    Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
    Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)
    For i = 1 To 10
        Calculate

        If Currow > 2 Then Currow = Currow - 1
        If Currow2 > 2 Then Currow2 = Currow2 - 1


        Cells(3, 2) = Worksheets("Sheet2").Range("C" & Currow)
        Cells(3, 4) = Worksheets("Sheet2").Range("C" & Currow2)

        With Range("V8:AC33")
            Range("B8").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        
        If Cells(1, 1) = 1 Then k = k + 1
    Next i
    Cells(2, 1) = k
End Sub

Although I can't see when the 2 greater than If tests will ever be false

 

[offthelip]

I think I am to blame for both of those, if you look back a my post#2 at that time there was no indication of how many loops we were doing so I put the "If Currow > 2 then" protection in, and it has stayed in even when not necessary.
I personally always use an "endif" if construction, this is just a habit after programming in lots of different languages most of which do require some formal end to an if statement.

 

[MARK858]

Even then it should be

If Currow > 2 Then
 Currow = Currow - 1
End If
If Currow2 > 2 Then
 Currow2 = Currow2 - 1
End If