Help with VBA

Mike7777

New Member
Joined
Dec 1, 2015
Messages
19
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.
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
try this code. I am note sure how you want to initialise the variable Currow, I ahve put in the workbook open and the worksheet activate subroutines:
Currow must be declared right at the top of the Sheet1 module
in the "sheet1" module
Code:
Public currow


Private Sub Worksheet_Activate()
currow = 36
End Sub


Private Sub Worksheet_Calculate()
If currow > 2 Then
 currow = currow - 1
End If
Cells(1, 1) = Worksheets("Sheet2").Range("B" & currow)


End Sub

in THISWORKBOOK module:
Code:
Private Sub Workbook_Open()
currow = 36
End Sub
 
Upvote 0
Thank you offthelip for your help I probably should have mentioned I have vba for Sheet1 for another task I needed done can I integrate your vba with what i have? Can you show me how?
###
Sub NETWORK()
'
' NETWORK Macro
'


'
Dim k As Integer
k = 0


For i = 1 To 1000
Calculate
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
###

Thank you
 
Upvote 0
I am not sure what you want:
I can see what your code does. ( although I don't understand why you copy the same range to the same place 1000 times)
What do you mean by "integrate" What do you want all of your final code to do?
 
Upvote 0
My version keeps track of the last move in Sheet2!Z1.

Right click your sheet's Tab, View Code, and paste:
Code:
Private Sub Worksheet_Calculate()
    Dim c As Integer
    
    c = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    With Worksheets("Sheet2")
        If .[Z1] = "" Then
            .[Z1] = "B36"
            Else
            'On Error Resume Next
            .[Z1] = .Range(.[Z1]).Offset(-1).Address
        End If
        [A1] = .Range(.[Z1])
    End With
    
    Application.Calculation = c
End Sub
 
Upvote 0
I am not sure what you want:
I can see what your code does. ( although I don't understand why you copy the same range to the same place 1000 times)
What do you mean by "integrate" What do you want all of your final code to do?


Its pasting to the left and its a calculation that has to be updated in this manner. I have it set to 1000 to do all those calculations for me without hitting F9 1000 times. Its just a time saver for what I'm working on.
And yes I was wondering if what you sent could be put in mine?
Thanks
 
Last edited:
Upvote 0
Code:
Sub NETWORK()
'
' NETWORK Macro
'
 
'
Dim k As Integer
k = 0
 
For i = 1 To 1000
Calculate
    Dim c As Integer
   
    c = Application.Calculation
    Application.Calculation = xlCalculationManual
   
    With Worksheets("Sheet2")
        If .[Z1] = "" Then
            .[Z1] = "B36"
            Else
            'On Error Resume Next
            .[Z1] = .Range(.[Z1]).Offset(-1).Address
        End If
        [A1] = .Range(.[Z1])
    End With
   
    Application.Calculation = c
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

Something like this? I please excuse me I dont know vba? But I'm working on it.
Could you show me how this would work?

Thank You
 
Upvote 0
The code that we posted runs when a calculation event occurs on that Sheet.

Your Sub, is public so it would go in a Module. You could easily put my sub into yours or call like any "sub"routine if you made it into a public sub and put into a Module. OF course then it only fires when you call it. e.g.
Code:
Sub Main()
    Sub1
    Sub2
End Sub

Sub Sub1()
    MsgBox "1"
End Sub

Sub Sub2()
    MsgBox "2"
End Sub
As shown in post #8 , after i=36, there would be no further changes to A1. The loop would continue to 1000.

I am not clear why you want to run calculate 1000 times. If you have a volatile function on the sheet and make changes with calculation mode as automatic, calculation event fires. Most people turn off calculation as I did so that speed is better.

I think explaining in more detail what you goals are might help us best help you. The calculation deal might be not be needed at all.
 
Last edited:
Upvote 0
The code that we posted runs when a calculation event occurs on that Sheet.

Your Sub, is public so it would go in a Module. You could easily put my sub into yours or call like any "sub"routine if you made it into a public sub and put into a Module. OF course then it only fires when you call it. e.g.
Code:
Sub Main()
    Sub1
    Sub2
End Sub

Sub Sub1()
    MsgBox "1"
End Sub

Sub Sub2()
    MsgBox "2"
End Sub
As shown in post #8 , after i=36, there would be no further changes to A1. The loop would continue to 1000.

I am not clear why you want to run calculate 1000 times. If you have a volatile function on the sheet and make changes with calculation mode as automatic, calculation event fires. Most people turn off calculation as I did so that speed is better.

I think explaining in more detail what you goals are might help us best help you. The calculation deal might be not be needed at all.

The worksheet is a machine learning system. The calculations are part of the learning process. The more calculations the smarter it gets.
Without going in great detail I use a rand function with just a few choices to set up a demographic for it to choose from. Now I want it to have to go through a history instead of having static choices.
I'm really bad with vba and I truly dont know how to put your vba into what I have.
Thanks again for your help
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top