Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA To Loop Vlookup Formulas


I have a basic client database on worksheet 'Database' and I have hundreds of 'forms' of identical size and layout on worksheet 'Proforma Template (2)'. These run vertically down the spreadsheet.

I wish to populate the forms from selected cells in 'Database'.

I used the folowing code to extract a client ref number from 'database' to 'Proforma Template (2)'

Sub Move_Ref()
Dim MyDirector As String
Dim MyRowCounter As Integer

On Error Resume Next
Sheets("Database").Select
Range("A38").Select
MyRowCounter = 0
MyDirector = "start"
Do Until MyDirector = "End"
MyDirector = ActiveCell.FormulaR1C1 'Place mouse at start of Folder List
ActiveCell.Copy........................


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Vlookup Looping (for Each...next?). Vlookup Loop Technique
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:

View Replies!   View Related
Vlookup + Right Formulas
I receive pdf files in which I have to copy multiple columns of data into a spreadsheet. The version of Adobe does not break the info out into seperate columns and the length of data in various columns varies from row to row. There are product names of various lengths followed by a planogram size and then some other data which is numerical seperated by commas, but is treated as text. Is there a formula which will look up a value in a cell and then report everything to the right of it?

PRINGLES SC & O 12 6,12,44,89
COMBOS PIZZA SNACKS 8 10,44,90,101

In the example above, the 12 and the 8 would be the POG size, which I have been able to extract, however, I would like to get all the info after that value into a seperate column. Can I combine vlookup and right to look up the POG size that has been moved into a seperate column to get that other info out?

View Replies!   View Related
Concactenate To Join Two Vlookup Formulas
Can you use two vlookup formulas as the text field in a concactenate formula? See below for example the formula I am using that is returning an #NA formula.

=CONCATENATE(VLOOKUP(A2,qacontact,6,FALSE),VLOOKUP(A2,qacontact,7,FALSE))

View Replies!   View Related
Function VLOOKUP At VBA Program Inside A FOR Loop
I want to use the function VLOOKUP at my VBA program inside a FOR Loop

View Replies!   View Related
Find VLOOKUP Formulas In A Sheet/book
Is the some way I can find out how many VLOOKUP formulas I have in a sheet and a workbook?

I can find them with ctrl F, but is there a what to return a number of how many instead of counting frome the Find box.


View Replies!   View Related
Call A Row Based On A Validation List With Vlookup Formulas Intact?
I am trying to create an interactive Price List / Quote Form. I have 1 tab (price list) that contains all data arrays. I have 1 tab (Items) that correctly calls avalable quantities based on a validation list and then Vlookup populates the formulas with the correct pricing & notes based on the quantity. I would like on the cover/quote page to have a drop down (in cells B23-30) where someone can choose a product based on the list, and then have the collums C,D & F populate with the rest of th information:

Column C with quantities for that product
Column D with pricing based on that quanity
Column F with notes for that quantity

Column E will calculate total based on simple math

Enclosed is my file

View Replies!   View Related
VLOOKUP And SUM Formulas: Pick Up Another Value In Column 9 And Should Add Value In Column 7 And 9 And Return It
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8

In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.

View Replies!   View Related
Loop Within A Loop (repeat The Loop Values)
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!


View Replies!   View Related
Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View Replies!   View Related
Copy Cells In Loop Based On Loop Increment Being Multiplied
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View Replies!   View Related
Loop Through Cells And Ranges Reverse Order With Backwards Loop
I am looping through each cell in a range and I would like to loop in reverse order.

Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next

I have tried:

For Each CELL In Range("C" & TotalRows, "C1")

and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.

View Replies!   View Related
Paste Formulas As Values (strip Out Unwanted Formulas)
I have a macro running this code to strip out unwanted formulas and formatting.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................

A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.

View Replies!   View Related
Avoid Changing A Loop Counter Within A Loop?
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.

The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.

Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?

View Replies!   View Related
Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately
I have this table

.......A.....B.....C....D
1.....I......a.....d.....g

2.....II.....b.....e.....h

3.....III....c.....f......i

As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

View Replies!   View Related
Nested Loop. Inner Loop Not Running
i have a problem with a nested loop:

it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.

am i doing something wrong?

dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================

Sub new2()

Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant

r = 1 ..................


View Replies!   View Related
For Each Loop To Delete Row W/ Value (Loop Backwards)
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,

Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")

For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c

View Replies!   View Related
Convert Formulas In Text Into Formulas
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?


View Replies!   View Related
Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)
I have a spreadhseet with various functions on it and what I am trying to do is this.

Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.

What I am trying to do is get cell F4 to return certain arguments.

E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"

I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")

Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")

If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"

Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?

View Replies!   View Related
Formulas Won't Calculate. Just Displaying As Formulas
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.

View Replies!   View Related
Do...Loop Error :: Loop Without Do
Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".

View Replies!   View Related
Endless Loop Within A Loop
I have loops working in other loops. The macro is almsot working well. It does the calculation i want but it fails to stop a loop, because of that, the macro can't run the next main loop (c), which is to move to the next cell where the calculations must be run.

I attach a file. the troubleshooting macroation is Sub Itiration.

The code of this macro are bellow. Basically, the loop using d as counter run into an endless loop. I don't how to stop this loop without affecting the results which are calculated correctly.

Sub Itiration()
Dim CurCell As Object
Dim TempSum As Double
Dim d As Integer

For c = 3 To Cells(3, 4)
If Cells(11, c) > 0 Then
For i = 1 To Cells(10, c)
Do

View Replies!   View Related
Loop Inside The Loop
I have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3

here is my main loop A1 = 5000
and second loop need to run inside the this loop

Sheet1.Activate
i = Range("A1")

For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b

View Replies!   View Related
For Loop Goes Into An Infinite Loop
My code below goes into an infinite loop, with culprit line shown in bold and underline...

View Replies!   View Related
Slow Down Loop At Each Loop
Going through a loop, I am trying to load pictures into an image box (or alternatively into a label) one by one i.e. going through the loop the first time, I want to load picture 1, then on the second loop, picture 2 and so on. A bit like an automated slide show.

I have written a simple loop and have used the loadpicture function to load the picture into the image box. When the code runs, the image box only gets populated after the last run through the loop. I have tried using application.screen updating function and the image.activate function without success. It is a simple bit of code and I expect an easy problem to solve if you know excel vba well.

View Replies!   View Related
Loop Gets Slower On Each Loop
I am parsing 15,000 files from a network server. The files are all the same format and length. The problem is that the first few iterations of my loop run fairly quickly, 7 to 9 seconds a case, but after only 300 iterations I'm up to 60 seconds a case. How do I keep the last iteration running as fast as the first iteration? I've included the main loop of my parsing routine below.

Sub Fill_Summary_Tabs() 'fills out the Datapack and JMP tabs
Application. ScreenUpdating = False 'turn off screen updating for speed

Call PrepImporterTab 'formats the Importer Tab so that everything runs smoothly < 1 second
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject") 'part of the filename test

For N = StartingCase To EndingCase '***** Start of the Parsing Loop *****

Sheets("Setup"). Cells(20 + N, 1).Value = Time 'Print the start time of each case....................

View Replies!   View Related
VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.

So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.

So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73

This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.

View Replies!   View Related
Hyperlinks And VLookup: VLookup To Find EMail And Web Addresses
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?

View Replies!   View Related
Vlookup Across Sheets, Nested Vlookup Possibly?
Iím trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that monthís interest payment. Can anyone help me figure this out?

The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. Itíd also be great if the formula can be transferred between workbooks. Iím not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.

View Replies!   View Related
Double VLookup (vlookup The Same Data From 2 Different Sheets)
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.

View Replies!   View Related
Using Vlookup & Indirect To Ref List And Vlookup Files
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

View Replies!   View Related
Vlookup Error Msg "unable To Get The VLookup Property Of The WorksheetFunction Class"
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.

I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?

View Replies!   View Related
Vlookup + Vlookup Discounting Any #n/a
I'm taking a spreadsheet that I produce each month and creating a year to date spreadsheet in the same format. I'm using a vlookup to find the campaign name in each sheet and add up the totals. This works fine but sometimes a camapign ends and so the vlookup for that month will produce an #n/a value so will reduce the whole sum to #n/a.

The VLOOKUP + VLOOKUP + VLOOKUP I was using that produced an #n/a is shown below.

=VLOOKUP($A6,'[Margin by Site Net April 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net May 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)+VLOOKUP($A6,'[Margin by Site Net June 2008.xls]Brighton'!$A$5:$F$26,2,FALSE)

To get round it I've added in an IF statement combined with ISERROR as shown below. It works but is looking quite messy. Is there an easier way to do this ? (the formula below is from the cell below the one above so the look up value is one cell down)

View Replies!   View Related
Do Until Loop ...
I have managed to successfully use a do until loop, however once finished, I would like it to move to the next row and do the same thing.

View Replies!   View Related
For Each Loop
I don't get For Each loops. All I want to do is cycle through everywork sheet in the active workbook and lock certain cells. I've got the lock part working right but I have no idea how to effectively preform this on each sheet. From what I've read I think a For Each loop is the best way, but I can figure out how it works.

View Replies!   View Related
Loop Until
I think this is somewhere close but first time I've tried doing loops.....

Sub Thing()
'Loop for Top 10

Dim i As Long
Dim FaultCode As String
Dim LastRow As Long
Dim SecondGoRow As Long
i = 7

LastRow = Range("N7").End(xlDown).Cells.Row

For i = 7 To 16

FaultCode = Range("N" + CStr(i)).Text
FaultCode = Left(FaultCode, 1)


View Replies!   View Related
For Each Next Loop
This is my first time using the "For Each Next" loop. I want to subtotal values in each sheet. Unless I make the worksheet active in the loop the code kept summing the same worksheet.

The question is do I have to activate the next sheet in the loop. Here is the code I have been using:

Sub SumCost()
Dim WS As Worksheet
Dim FinalRow As Integer
For Each WS In ActiveWorkbook.Worksheets
WS.Activate
FinalRow = Cells(Rows.Count, 11).End(xlUp).Row
Range("k" & FinalRow + 1).Formula = "=sum(k2:k" & FinalRow & ")"
Range("A" & FinalRow + 1).Value = "Total"
Next WS
End Sub

View Replies!   View Related
Do Loop Until Loop
This works to locate the first instance found:

View Replies!   View Related
Add New Row In Loop
I now wonder is it possible to add Text in the row above and below the loop can this be done? Like below?

Image
Test 1 Test
Image/
Image
Test 2 Test
Image/

View Replies!   View Related
Loop Without Vba
if it was possible to make a loop without using VBA?

I have the following problem:
If the value of "J14" > 50; then I want to increase "J2" until "J14" < 50

Is this possible without VBA??

See attachment for details:

View Replies!   View Related
Next OR Do....Loop
I have a spreadsheet that uses columns A:I. I want excel to be able to look at a row in the spreadsheet and where column I is empty it should delete cells I and H, then look at cell G and if it is empty, delete cells G and F, and then do exactly the same for columns E and C.

also, this spreadsheet is not always the same length, but always starts at row 15

View Replies!   View Related
How To Use For Loop
I has a long rows of data, but I just want to read four rows of data each time,how can I code it using for loop?

View Replies!   View Related
Loop Or For Each Within A For Each
I am having some technical difficulties trying to place data onto sheet2. Sheet2 starts out blank, as sheet1 is proccessed it pastes data to sheet2. I have tried a "For Each" that failed only pasting data into Range "A1" for every found instance.

In the code below, the area colored "Magenta", I need a Loop of some type that as data is pasted into column A on sheet2, it indexes to the next available cell and continues. How do I construct such a Loop or For Each with in the existing For Each?

View Replies!   View Related
Do Until... Loop
I don't know what I am doing wrong, but I know this should be verrrrry simple, and for some reason I just can't get it to work. I have the following

Sub HighlightRawData()
Do Until Selection = "x"
If Selection < 0 Then
EntireRow.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else: Selection. Offset(1, 0).Select
Exit Do
Selection.Offset(1, 0).Select
Loop

End Sub

This code is supposed to look at each cell in column A, and if it is not blank, it is supposed to highlight the entire row yellow and move down one row. If the cell is blank, I want it to just move down to the next row. The loop is supposed to run until it hits the "x" at the end of the range of cells.

View Replies!   View Related
Loop Without Do
I was using Loop without trouble until I used If...Else inside the Do...Loop. The error I get is: Loop without Do.
Why is that?

Sub Macro3()
(doing something)
Do Until (statement)
(doing something)
If (statement) Then
(doing something)
Else
(doing something)
Loop
End Sub

View Replies!   View Related
Formulas Are Not A Value
Do
If ActiveCell.Value Is Value Then
ActiveCell.Select
Else
ActiveCell. Offset(1, 0).Select
End If
Loop Until ActiveCell.Value Is Value





For some reason when you have a formula in a cell but no data, it says its greater than zero...but because there is no data in that cell, but only a formula, is there anyway to get this code to work.

View Replies!   View Related
Add Row With Formulas
I've got a generic question here about adding a row with formulas above the subtotal line.

In the table below I have some simple rows of sums and a subtotal row at the bottom.

If a macro is run how can I insert a new row with the same two formulas in the row one above the Subtotal row....

View Replies!   View Related
If Formulas In VBA
I have a sheet of data which is refreshed eash day, the data has frequencies and values in it. I need the code to say:

if column E:E = Monthly, and column M:M = Annually then divide the value in column N:N by 12

If column E:E = Monthly, and column M:M = Quarterly then divide value in column N:N by 4

We have these four freqencies:

Monthly
Quarterly
Half Yearly
Annually

and the above code will need apply to all scenarios i.e. if E:E = Quarterly and M:M = Monthly then x N:N by 3.

E:E being the origonal frequency and M:M being the new one, we need to know the value of the new gift at the old frequency.

View Replies!   View Related
Tax IF Formulas
I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000$ and for 25%, 11,500$.

In D14, I have as a taxable income, 20,000$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...

View Replies!   View Related
Add New Row With Formulas
I am working on a sheet that will have a large range of rows used. There is formulas within a few cells in each row specific to that row. When the user enters data into colum A of the last empty row would there be a way to insert two new rows below that row with formatting and formulas? The toughest part for me has been keeping the totals at the bottom updated. I attached the sheet to help explain if I haven't done a very good job at explaining it.

View Replies!   View Related
For/Next Loop Won't Start
I'm sure I just need a change in the line of code in red, but can anyone see why when the code reaches the For/Next loop in red it just jumps over it and goes to the End With line?

FYI - The code is supposed to check (select) the boxes in ListBox1 if any item in the list it's creating matches the value found in Sheets("Zone Associations").Cells(Rng, sZone)


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved