?
Solved

VBA Code to find name and populate cell

Posted on 2011-09-08
4
Medium Priority
?
215 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 59

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

579 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