Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 22nd, 2011, 01:18 AM
Registered User
 
Join Date: Jun 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default to iterate within selected range and loop

I want to iterate values within various ranges
For eg: I have total of 720 input values.these values should be taken from an input excel sheet. and then calculate the output using the specified formulae. and then write the results to another sheet.

The input values consist of 2 columns containing 720 values in sheet called INPUT. the other input data is obatained through directly entering data in the textbox in userform

i want output values between different ranges of those 720 values.
In sheet1. i want to calculate the values using formulas from o to 720

In sheet2. i want to calculate the values using formulas from 181 to 720 and then 0 to 180 (the output should be in such a sequence)

In sheet3. i want to calculate the values using formulas from 361 to 720 and then 0 to 360 (the output should be in such a sequence)

In sheet4. i want to calculate the values using formulas from 541 to 720 and then 0 to 540 (the output should be in such a sequence)


.I have got the code for calculating the values from o to 720. but im not able to get the particular loop

Code:
Public Sub end_button_Click()
Unload userform1
End Sub

Public Sub ok_Click()
Dim mr As Variant, m As Variant, m0 As Variant, lam As Variant, a2 As Variant
'to get the input values from the userform
	strke = Val(stroke.Text)
	d = Val(dia.Text)
	conrod_length = Val(l.Text)
	cg_conrod = Val(l_dash.Text)
	pist_mass = Val(pmass.Text)
	conrod_mass = Val(conmass.Text)
	crankpin_mass = Val(crank_mass.Text)
	mr = (pist_mass * (conrod_length - cg_conrod) / conrod_length) + crankpin_mass
	m = (cg_conrod * conrod_mass / conrod_length) + pist_mass
	r = strke / 2
	lam = r / conrod_length
	a1 = 1
	a2 = lam + ((1 / 4) * (lam) ^ 3) + ((15 / 128) * (lam) ^ 5)
	cr_m1 = Val(crank_mass1)
	cr_r1 = Val(crank_rad1)
	cw_m1 = Val(counter_mass1)
	cw_r1 = Val(counter_rad1)
	cr_m2 = Val(crank_mass2)
	cr_r2 = Val(crank_rad2)
	cw_m2 = Val(counter_mass2)
	cw_r2 = Val(counter_rad2)
	eqm1 = (cr_m1 * cr_r1) - (cw_m1 * cw_r1)
	eqm2 = (cr_m2 * cr_r2) - (cw_m2 * cw_r2)
	m0 = (eqm1 + eqm2)
'the loop
	Dim k As Variant
	For f = 2 To Selection.CurrentRegion.Rows.Count - 1
		Set i = Worksheets("input").Cells(f, 1)
		Set k = Worksheets("input").Cells(f, 2)
		y = rad(i)
		p = k * 0.1
			Cells(f, 16).Select
			Selection.Value = p
		a = Area(d)
		fz = p * a
			Cells(f, 17).Select
			Selection.Value = fz
		fzm = Forcezm(r, mr, m0, y, m, a1, a2)
			Cells(f, 18).Select
			Selection.Value = fzm
		fym = Forceym(r, mr, mo, y, m)
			Cells(f, 19).Select
			Selection.Value = fym
		vp = vprmf(r, m, y)
			Cells(f, 20).Select
			Selection.Value = vp
		vs = vsrmf(r, m, a2, y)
			Cells(f, 21).Select
			Selection.Value = vs
		trf = vp + vs
			Cells(f, 22).Select
			Selection.Value = trf
		rf = trf - fz
			Cells(f, 23).Select
			Selection.Value = rf
		fn = pistonst(rf, lam, y)
			Cells(f, 24).Select
			Selection.Value = fn
	Next f
End Sub

		Function Area(d As Variant) As Variant
		pi = 22 / 7
		Area = (pi * (d ^ 2)) / 4
		End Function

	Function Forcezm(r As Variant, mr As Variant, m0 As Variant, y As Variant, m As Variant, a1 As Variant, a2 As Variant) As Variant
	pi = 22 / 7
	rpm = Val(speed.Text)
	Forcezm = r * (((2 * pi * rpm) / 60) ^ 2) * (((mr * m0) * 0.001 * Cos(y)) + (m * 0.001 * a1 * Cos(y)) + (m * 0.001 * a2 * (Cos(2 * y))))
	End Function

		Function Forceym(r As Variant, mr As Variant, m0 As Variant, y As Variant, m As Variant) As Variant
		pi = 22 / 7
		rpm = Val(speed.Text)
		Forceym = r * (((2 * pi * rpm) / 60) ^ 2) * ((mr + m0) * 0.001 * Sin(y))
		End Function

	Function rad(i As Variant) As Variant
	pi = 22 / 7
	rad = (i * pi) / 180
	End Function

		Function Pre(p As Variant) As Variant
		Pre = p * 0.1
		End Function

	Function vprmf(r As Variant, m As Variant, y As Variant) As Variant
	pi = 22 / 7
	rpm = Val(speed.Text)
	vprmf = r * (((2 * pi * rpm) / 60) ^ 2) * m * 0.001 * Cos(y)
	End Function
		
		Function vsrmf(r As Variant, m As Variant, a2 As Variant, y As Variant) As Variant
		pi = 22 / 7
		rpm = Val(speed.Text)
		vsrmf = r * (((2 * pi * rpm) / 60) ^ 2) * m * 0.001 * a2 * (Cos(2 * y))
		End Function

	Function pistonst(rf As Variant, lam As Variant, y As Variant) As Variant
	pistonst = (rf * lam * Sin(y)) / ((1 - (lam * lam) * ((Sin(y)) ^ 2)) ^ 0.5)
	End Function
 
Old July 9th, 2011, 11:12 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Can you please let us know if your problem is in identifying the last cell etc or of the problem is in looping through the cells 1 to 720 etc

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Use a macro to sort a user selected range rmccafferty Excel VBA 3 April 26th, 2011 04:32 PM
How to loop through a range? chobo XSLT 3 May 24th, 2008 03:50 PM
To iterate the FOR loop variable garg.ashish15 Oracle 0 November 10th, 2006 09:31 AM
Putting selected range addresses in an array SMI Excel VBA 4 July 7th, 2006 04:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.