Wrox Programmer Forums
|
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 3rd, 2011, 05:44 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default VBA Loops

Hi

I have a loop which loops through a cell range and checks multiple things and returns results in multiple columns within a another sheet.

Code:
Nrow = 2

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = Not cell.Interior.Color = RGB(217, 217, 217)
    Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = cell.Interior.Color = RGB(255, 255, 0)
    Sheets("Input_Reference_Table").Cells(Nrow, 11).Value = cell.Column
    If Right(cell.NumberFormat, 1) = "%" Then
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
    ElseIf Right(cell.NumberFormat, 1) = "0.000%" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt(cell.NumberFormat)
    ElseIf Right(cell.NumberFormat, 1) = "0.000" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)
    Else
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell
I need add a line of code to the above code so for each cell its returns in the column H in sheet Input_Reference_Table "(the value in corresponding cell in column E) & " - " & (first row of the corresponding column)"

However i am not sure how to do this in a loop, Does anyone know?

Thanks

Jeskit
 
Old May 3rd, 2011, 05:58 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

i had a go at writing the line of code to add to my loop but it doesn't brng back the cell value for example it brings back "E6 - T1"
Code:
    Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "E" & cell.Row & " - " & Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.column, 1) & "1"

Last edited by jeskit; May 3rd, 2011 at 06:20 AM..
 
Old May 3rd, 2011, 06:22 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

No worries i have figured it out!!!

Code:
Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = Range("E" & cell.Row).Value & " - " & Range(Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.column, 1) & "1").Value





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Loops jeskit Excel VBA 3 February 4th, 2011 03:34 AM
break for-each loops, or limit amount of loops warhero XSLT 2 July 4th, 2007 02:18 AM
Loops deontae45 VB.NET 2002/2003 Basics 2 September 28th, 2006 03:48 PM
Two Loops iloveoatmeal Classic ASP Basics 5 September 26th, 2005 09:59 AM
While loops and For loops in XSLT spencer.clark XSLT 1 August 5th, 2005 09:50 AM





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