Solved

VBA Code to find name and populate cell

Posted on 2011-09-08
4
204 Views
Last Modified: 2012-05-12
Using xl2007

What I am looking for the code to do:

(i) would be the row number as we loop through

If the Cell in Column E, Row (i) = "RB"
then find the first cell ABOVE it in Column E that Says "C".  
The value from column D on the Row containing C needs to go into Column I on row (i)
The value from Column F on the Row containing C needs to go into Column J row (i)

If the Cell in Column E, Row (i) does not equal "RB" then move on to the next row

Any assistance is greatly appreciated!

Thanks, Brandon
0
Comment
Question by:KnutsonBM
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
So, are you looking for a function, that will be called passing it "i"?

~bp
0
 
LVL 6

Author Comment

by:KnutsonBM
Comment Utility
no, no functions, just some simple vba code to loop through the dataset and populate Columns I and J
0
 
LVL 6

Author Comment

by:KnutsonBM
Comment Utility
DOH, i figured it out, sorry to bother yall:

Sub FindResp()

Dim Z As Integer
Dim i As Integer


i = 2


Do Until Cells(i, "A").Value = ""

    If Cells(i, "E").Value = "ROUTE BACK" Then
   
        Z = i
       
            Do Until Cells(Z, "E").Value = "COMPLETE"
           
                Z = Z - 1
               
            Loop
           
            Cells(i, "I").Value = Cells(Z, "D").Value
            Cells(i, "J").Value = Cells(Z, "F").Value
           
    Else
    End If
   
    i = i + 1
   
Loop
               
           
End Sub

if anybody has suggestions for better coding i would gladly take them.........

-Brandon
0
 
LVL 12

Accepted Solution

by:
josika earned 500 total points
Comment Utility
Try this:

Set oExcel = CreateObject("Excel.Application")
Set oWorkBook = oExcel.Workbooks.Open ("c:\Sheet.xlsx")
Do Until oExcel.Cells(i,"a").Value = ""
	If UCase(oExcel.Cells(i,"e").Value) = "RB" Then
		x = i - 1
		Do Until x = 0
			If UCase(oExcel.Cells(x,"e").Value) = "C" Then
				oExcel.Cells(i,"i").Value = oExcel.Cells(x,"d").Value
				oExcel.Cells(i,"j").Value = oExcel.Cells(x,"f").Value
				Exit Do
			End If
			x = x - 1
		Loop
	End If
	i = i + 1
Loop

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now