Solved

VBA Code to find name and populate cell

Posted on 2011-09-08
4
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 54

Expert Comment

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

~bp
0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 36502794
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
ID: 36502813
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
ID: 36502817
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 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