Loop through a cell with different value serially in excel

Question

1.00/5 (1 vote)

See more:

Hello,
I Have a situation Please help me to solve this.

I have a Excel file with 2 sheets named as

Sheet1 & Sheet2

Sheet1 has "INPUT" + "Complex Formulae" = "Result"(again with complex formulae) Columns
Actually the case is that the sheet1 has a lot of complex formuales & I will get some result finally after a lot of calculations

Sheet2 has only "Input Numbers" & "Results" Columns.
A lot of input numbers(random) which need to go to sheet1 input column one by one & Results column should fill automatically after the result of that input.

Sheet Attached as a reference. Sheet Link: Looping.xlsx - Google Sheets[^]

Thank you.

What I have tried:

I Have tried to write a macros by using for each loop, which will take cell from range but it is not going to get result from another sheet.

Sub test1_Click()
    Dim x As Integer
    
    Start_open = Sheet2.Range("A3").Value
    
    End_open = Sheet2.Range("A5").Value
    
    For x = Start_open To End_open
    
    Sheets("Sheet1").Name = "Sheet1"
    Range("A5").Select
    ActiveCell.Value = x
    
    
    
'    Z = 5
'    y = 3
'
'        For i = Start_open To End_open
'        Cells(Z, y) = i
'        Z = Z + 1
'
'        Sheets("Sheet2").Name = "Sheet2"
'        Range("C3").Select
'
'        Next
        
    'MsgBox x
    
    Next
    
End Sub

Posted 9-May-17 3:58am

Member 13003315

Updated 9-May-17 10:07am

CHill60

v4

Add a Solution

Comments

Maciej Los 9-May-17 15:46pm

The question is not clear. The code you're trying to is not clear too. Can you provide more details?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-05-09T10:07:00

Please, read my comment to the question first. The comment to the solution #1 provided by CHill60[^] will be helpful too.

Let's say you want to write 10 random numbers in Sheet1 but you want to get minimal and maximal values to randomize from Sheet2.

Take a look at example:

VB

'force explicit declaration of all variables in that module
Option Explicit

Sub WriteRandomNumbers()
'define variables for source and destination worksheets
Dim SrcWsh As Worksheet, DstWsh As Worksheet
'define min and max
Dim iMin As Integer, iMax As Integer
'random number
Dim rndValue As Integer
'iterator
Dim i As Integer

'on error got error handler
On Error GoTo Err_WriteRandomNumbers

'initiate variables
Set SrcWsh = ThisWorkbook.Worksheets("Sheet2")
Set DstWsh = ThisWorkbook.Worksheets("Sheet1")

iMin = SrcWsh.Range("A2")
iMax = SrcWsh.Range("B2")

'loop through cell 1 to 10 in Sheet1 - column A
For i = 1 To 10
    Randomize
    rndValue = Int((iMax * Rnd) + iMin)
    DstWsh.Range("A" & i) = rndValue
Next

'exis sub-programme
Exit_WriteRandomNumbers:
    'ignore errors
    On Error Resume Next
    'clean up!
    Set SrcWsh = Nothing
    Set DstWsh = Nothing
    Exit Sub

'error handler
Err_WriteRandomNumbers:
    'inform about error
    MsgBox Err.Description, vbExclamation, Err.Number
    'go to exit subroutine
    Resume Exit_WriteRandomNumbers
End Sub

CHill60 · Answer 2 · 2017-05-09T06:02:00

It is not clear why on earth you would attempt to rename the sheets within the loop - especially to the same name that they were.

If your sheets are called something other than "Sheet1", "Sheet2" etc then the line(s) like

VB

Sheets("Sheet1").Name = "Sheet1"

will fail. If you really want to do something like that then use

VB

Sheets(1).Name = "Sheet1"

(Note Excel sheets start numbering from 1 not 0 like VB itself)

Again I don't see what the problem is - because you are using a range from Sheet2 in the For loop it is getting the values from Sheet2. I'm guessing your problem is that all of the values are being inserted into Cell A5 on Sheet1. If you want to progress down that sheet within the loop then just adjust the Range reference as you go like this

VB

Sub test1_Click()
    Dim x As Integer
    
    Start_open = Sheet2.Range("A3").Value
    End_open = Sheet2.Range("A5").Value
    
    Sheets(1).Name = "Sheet1"
    
    Z = 5
    
    For x = Start_open To End_open
    

        Range("A" + CStr(Z)).Select
        ActiveCell.Value = x
        Z = Z + 1
   
    Next
    
End Sub

If you were trying to put the information somewhere else, on Sheet3 for example then you have to activate the sheet before you can use it.
This line for example

VB

Sheet3.Range("A" + CStr(Z)).Select

results in an "Application-defined or object-defined error" unless you also include

Sheet3.Activate

I hope this helps. If not then use the Have a Question or Comment? link next to this solution to explain your problem in more detail

Loop through a cell with different value serially in excel

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0