Match And Copy: Declare The Variables Correctly And Have The Operations Done In A Series Of Do/ Loops Or For/Nexts
Feb 2, 2007
I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.
Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.
I know that we should declare all variables at the beginning of a subroutine, in fact I'm told it's good practice to use Option explicit to 'force' variables to be declared, my question is why?
If I don't declare a variable the routine still seems to work OK so what is the downside of not declaring them upfront? Is it just for neatness or common practice or is there another reason?
Sub checkPO() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False Dim Rng As Range, MainSheet As Worksheet, item As Variant ', CurrWidth As Integer, UnitWidth As Integer
Set MainSheet = Sheets("Orders To Chase") Set Rng = MainSheet.Range("B5", MainSheet.Range("B60000").End(xlUp)) totalqueries = MainSheet.Range("B5", MainSheet.Range("B60000").End(xlUp)).Cells.Count
UnitWidth = 282 / totalqueries
With progbar '\displays the please wait box .Show False
.prog.Width = 0 '\ updates progress bar .Repaint End With
If I declare the variable my progress bar goes off screen. remove it and its back to within its box.
I haven't declared any of these variables either. Does this matter?
i need to set the range of variables that user can add to the range.
For Example:
AA_* BB_* CC_* ABCD_*
so we accept variables STARTING with AA_ OR BB_ OR CC_ OR ABCD_. If the user enters sth else, then I want to disable the "Enter" key. (If the Cell is Empty than it is also OK!!)
If disabling the Enter key is not possible then maybe i can use Conditioning Formatting? But the question is then if i can use for single condition OR statement.
I'm trying to better understand loops and variables. I have an instance I'd like to try and implement but I'm struggling with it. I know how to set a variable as 1 and loop through the code increasing the number each time but this one is a little different.
At the start of my code, I scan the worksheets for the worksheet names -
Monday Tuesday Wednesday etc etc
and then assign these worksheets as
Monday = ws Tuesday = ws2 Wednesday = ws3 etc etc
That way in my code I can do things to the workbooks like -
ws.Range("D4").Value = "blah blah" and always know it will be tackling the right worksheet regardless of the sheets position in the workbook or sheetcode.
Well I have lots of changes to make on each of these sheets (Monday -> Sunday) which are identical. What I'd like to do is something like this -
For each VARIABLE in (MY VARIABLES WS,WS2,WS3 ETC) VARIABLE.Range("D4").Value = "blah blah" Next VARIABLE
Is it possible to use a variable to change a controls name in a For Loop? I have several worksheet combo boxes that have the same root name, e.g., cbobox1, cbobox2. cbobox3, etc. I want to be able to keep the main name and change the numeric part in a for loop and also perform some function with the control. For example:
For x = 1 To 10 Worksheets("Name").cbobox(x).Clear Next x
I have written the following snippet in my code, the purpose of which is to cycle through the text boxes on a form (frmPaymentOrders) and transfer the value of each text box txtPO1 to 10 to the variable PORow (Dim as Long). These will then be pasted into the correct part of my worksheet.
But I keep getting an "Invalid Qualifier" error - even though I think I have correctly qualified the PORow variable as Long.
Code: For i = 1 To 10 PORow.Value = ["frmPaymentOrders.txtPO" & i] Next i
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.
I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.
I'm trying to copy a cell from one workbook and paste it into another workbook. Both workbooks are open so that is not the problem. The problem I'm running into is that in the first workbook where I'm getting the information the cell is tied to another. And the cell it gets the information from is running a COUNT function. So the problem I run into is that when I go to copy the cell and paste it into the new workbook I'm getting 0 instead of the correct number found on workboook1.
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.
Example: I renamed cell A1 to Apple, A2 to Banana, A3 to Chair. Down in cell a50 I was using simple formulas a1+a2*a3. then copying the formula into column b50, c50,... Now when I use the formula SUM(Apple+Banana*Chair) and try to copy it into cell b50, It doesnt change the formula to SUM(B1+B2*B3).
I have two sheets in my workbook, and I am trying to reference one chart's value into the other. (attached)
I have referenced successfully by manually typing in the reference on row 2 for all values on Sheet 1: Sheet 1 - C2 needs to reference Sheet 2 - B2. (Sheet2!B2) - no problem all the way across to F2. All those values are correct b/c I manually typed in the reference.
The problem comes when I just want to drag over that same formula to increment for the other cells. It's not incrementing the formula like I need it to.
So, Sheet 1, Row 2 shows how the correct values I need to pull over but was done in a manual way. Sheet 1, Row 5 shows how it increments incorrectly when I try to just drag/copy the formula over. What I need is for Sheet 1 - D5 to reference Sheet 2 - C3, but instead if I try and drag to copy the formula instead of manually typing it in, it increments when I drag reference D2 instead of C3.
I'm having problems with the MATCH function. If I use =MATCH(A2,'c:myfile.xlsx'!MyRange,0), it works. A2 is the value that I want to look up. 'c:myfile.xlsx'!MyRange is the full path of a range in an another XLSX.
However, if I assign the file name to a cell and try: =MATCH(A2,A3,0). where A3 has the exact same path as the line that work, Excel returns #N/A. I also tried a match on a range in the same tab of the same spreadsheet, and it didn't work. Is it possible to do what I'm trying to do? The file location of the external XLSX is going to change, and I wish I could just update one cell rather than every single cell that contains a MATCH.
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
I have a worksheet that has 6 columns of numerical data that all column datasets are referenced together for each row. So for example the data in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2 columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of these must be in the same row or I need the closest value. When these values are found in columns A & B, return the values from the same row in Columns C through F.
I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.
I am very new withe macro I recieve every day a CSV file from our supplier withe a list of the products that got updated withe new price, stock count, product ID etc.
I have my own worksheet with the product ID that we use, and I want to finde the exact match to my product ID in that CSV file and delete all other that don't match.
But i need them get deletede by rows thos product ID that dosen't match to my workbook.
I tried with this, so it could set an X in front of my match then i could filter and copy it to my workbook but it dosen't work: Because the same product ID is sometime in 100 rows and the X come only in front of one of them.
=IF(ISNA(MATCH(Q2:Q1000;G$2:G$1000;0));"";"X")
so with some macro. I need to have every row deleted that don't match to my list of product ID.
I have 2 worksheets named sheet1 and need_to_delete that are in the same format: 7 digit number, 5 digit alpha numeric, text, dollar amount. I need to copy every row into sheet2 where there are no matches in the column A of the two source worksheets, and copy every row that does have a match in column A into sheet 3. I also need to keep the rows in their current order.
I'm trying to take a vba function and add a second if clause. Here's the function:
VB: Function getdes(DRng As Range, LURng As Range) For Each ce In LURng If ce.Value = DRn Then holder = holder & ce.Offset(0, 2).Value & vbLf & "" End If Next ce getdes = Left(holder, Len(holder) - 2) End Function
I want to do something like:
VB: Function getdes(DRng As Range, LURng As Range) For Each ce In LURng If ce.Value = DRn If ce.Offset (0,-02).Value = "y" Then holder = holder & ce.Offset(0, 2).Value & vbLf & "" End If Next ce getdes = Left(holder, Len(holder) - 2) End Function
Does excel contain a format of : "number of months"?
Example : I have 3 cells A1 : should contain the number of months (3 months) A2 : should contain a date with this format (dd/mm/aaaa) A3 : should contain A1+A2
I am using following codes to find and delete number in series.
HTML Option Explicit Private Sub CommandButton1_Click() Dim Rng As Range, i As Long, r As Range, lVal, uVal Dim DeleteCount As Double Dim lRow As Long If Val(Me.TextBox1) > Val(Me.TextBox2) Then MsgBox "End number must be greater than start number" Me.TextBox2.Value = "" Me.TextBox1.SetFocus Me.TextBox2.SetFocus Exit Sub End If lVal = Val(Me.TextBox1): uVal = Val(Me.TextBox2) Application.ScreenUpdating = 0 For i = 1 To Sheets.Count With Sheets(i) Set Rng = .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell)) For Each r In Rng If r >= lVal And r
I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".
Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?
The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))
Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?
I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator. In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!
'Initializing tagrow so we can test what train we are trending' tagrow = Cells(12, 2).Value
'Only Propylene flows need adjustment in A and B trains, calculate everything' If tagrow = 7 Or tagrow = 8 Then Sheet2.Calculate Range("D15:D115").Value = Range("C15:C115").Value / 1000 End If
I need to have the contents of cell A1 (a 3 component list) control a formula in cell E1. If A1 is "add", then E1 should be B1 x D1. If A1 is "delete", then E1 should be B1 x D1 x .6, and if A1 is "delete ilo", then E1 should be B1 x D1 x (-1).
A1 B1 C1 D1 E1
list qty. list price of C1 item formula needed ( lookup)
