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 May 23rd, 2011, 09:56 AM
Friend of Wrox
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Looping through a cell range


This probably an odd problem but i am stuck and i have no idea where to start in solving it so i am hopign someone can help.

I have a workbook which has two tables on it one table just has values and the other table has forumla in it. the two tables are identically except for the contents of the cell.

What i need to do is create a macro which opens this workbook and also create a new workbook and then loop through the table with the forumla and look at the forumla and then edit the forumla so it uses the variables in the other table.It should then take that value and paste into the new workbook in column 4 of the sheet added and for each cell in teh range move to the next cell in column 4 of the new workbook.

For example if the forumla is A1 / A2 then i would need it to look at the correpsonding table and look at correpsonding cells and grap the values edit the fourmla so it comes out like b10000 / b20000

So far i have written the macro to create the first bit which opens the workbook, creates the new workbook and loops through the cell range and looks at whether its got text but then i cant think how to do the next bit.

I should explain the variables. PPCWBSht is the sheet within the workbook, PPFWB is the workbook with the tables.

'Version number of Input Reference Form to be convert
ConvertVN = Application.InputBox("Please enter the version number of the Input Reference Form to be convert", "Convert Version Entry Box", SConvertVN)

'Is previously manually entered data to be imported
ManualVN = MsgBox("Would you like any data entered manually in previous versions to be imported", vbQuestion + vbYesNo, "Manual Version question box")
    If ManualVN = vbYes Then
        ' If yes to ManualVN then what version number to import from
        ManualImportVN = Application.InputBox("Please enter the version number of Calculations Table the data is to be imported from", "Manual Version Entry Box", SManualVN)
    End If

'Set variables
strFldr = "R:\HondaCarsEurope\Markets\Germany\PPIII\Tables"
Set PPCWB = Application.Workbooks.Add
PPCWB.Sheets.Add.Name = "CalcAPD"
Set PPCWBSht = PPCWB.Sheets("CalcAPD")
Set PPFWB = Application.Workbooks.Open(strFldr & "/" & "HDE_PPIII_MONTH_Input_Reference_form_V" & ConvertVN & ".xlsx")

'Add column titles to new workbook
Application.StatusBar = "Open New workbook"
ColN = 1
PPCWBSht.Cells(ColN, 1).Resize(, 8).Value = Array("CalcID", "CalcDescription", "Calcname", "Calculations", _
"Department", "Category", "NumFormat", "ChartOrder")

'Loop through the form cells and populate the calculations table
Application.StatusBar = "Loop through calcs Form and populate calculations table"
For Each cell In PPFWB.Sheets("IRFORM").Range("F4:U1000")
    If cell.Value <> "" Then
    End If

Next cell

I am hoping i have explained in enough detail and that it makes sense. If anyone could help me it would be great
Old May 25th, 2011, 09:00 AM
Friend of Wrox
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts


I have been thinking about it and what i need to do is program the macro so it:
  • First looks into the cell and extracts the forumla.
  • Then go to the cell locations in the forumla (but the column part would be -6 to go to thecorrsponding cell in the second table).
  • Then extracts the values in those cells.
  • Take the orginal forumla and replace the cell location in the forumla with values.
  • Then paste that string into the new workbook in column 4 (starting from row 2) and moving to a new cell for each cell in the range.

I dont know if this is possible but i am going to have a go at building it step by step. Fingers crossed

Thanks jeskit
Old May 25th, 2011, 06:47 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

where do you work anyway jeskit? are they looking for excel developers? i need a job and it'd take the programming load off of you, lol. anyway, you can get at the text of the formula with something like
Sub sGetFormula()
Dim stFormula As String
stFormula= Sheet1.Range("A1").Formula
End Sub
seems like a harsh way to go about it though. Not sure there's a better way than looking at the formula as a string and testing it thus. Maybe you could put a column in the table that uses an if() function and returns the appropriate formula if it has to?
Anyway, let me know some more, I'm going to be living in a homeless shelter soon, so i may not be immediately available, but I'll check in when i can to help you through this issue. Btw, thanks for clicking the "Thanks" button. You're the only one who ever has, lol.

Similar Threads
Thread Thread Starter Forum Replies Last Post
cell is empty while using For Each cell In Range jase2007 Excel VBA 4 April 5th, 2012 10:20 PM
Name a range simply for each third cell wapfu Excel VBA 2 December 6th, 2006 03:04 PM
I need to refer a cell within a cell like =RC[ RC2 chakravarthi_os Excel VBA 1 September 24th, 2006 08:19 AM
Lose cell Text when editing cell in VSFlexGrid 6 bobcratchet VB How-To 0 July 30th, 2004 09:32 AM
Looping thro' / Copy from Range iwat03 Excel VBA 1 July 3rd, 2003 11:47 AM

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