Understanding How FindFirst Works

Mar 5, 2008

How do I use Find to locate the first occurence of a particular value in a cell, and return the row that the cell is in?

View 14 Replies


ADVERTISEMENT

Not Understanding VLOOKUP

Jun 30, 2008

I have searched for a while on VLOOKUP (something I'm not very familiar with but and trying to become more so), And if this wasnt such a time sensitive project I would continue to search. In one sheet, there is a column which includes employee #'s, the next column over is the employee names, next is salary. On another sheet I put a drop down box where you can select the employee number. In the cell next to that I would like to have the employees name come up as well as the salary in the next cell over....something like this

Name Salary
Dropdown w/#'s

Taken from


Emp# EmpName EmpSalary
23123 Joe Smith $50,000

Is this the right time for vlookup...there are almost 1000 names in this sheet?...

View 9 Replies View Related

Understanding Template

Dec 8, 2008

I have a workbook with several worksheets and numerous userforms, commandbuttons and macro's. I want to prevent people from saving over the workbook once they have used it, but allow save as using a different workbook name.

I tried saving the book as .xlt, but then when I reopen it and make material changes, it lets me save over it. I have't worked with templated before, is there something I am doing wrong?

View 9 Replies View Related

Sumproduct Understanding Cell Value

Mar 26, 2014

I have this formula -

=SUMPRODUCT(('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$E:$E=B4)*('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$D:$D=$A$2))

Which works perfectly if A2 has the cell value 24/03/2014 however, if i put in $A$2 this value

="02"&"/"&$XFC$1&"/"&"14", where XFC$1 equals =IF(XFD1>9,XFD1,"0"&XFD1) and XFD1 = =MONTH(TODAY())

Which in basic term gives the month value "03"

The cell shows the value 24/03/2014 however does not work with the sumproduct.. is there anyway to get sumproduct to read this cell value as 24/03/2014 as the whole point is so every month I don't have to update anything and it will constantly update...

The only alternative i can think of is quickly making a VBA script to hardcode the cell values for the dates and change the month value for the middle of the cells every month.

View 3 Replies View Related

Understanding Automated Email Code

Dec 19, 2006

it has been designed to send out automated emails using Excel and Lotus Notes, unfortunately the code was give to me to modify and the person who created it didn't comment the code at all so i'm finding it impossible to see what each part is doing: ....

View 14 Replies View Related

Understanding Code: Extend The Analysis

May 2, 2006

i need to extend the analysis but i have no idea what the 2 modules below do.

Sub mmm()
zonecode = Worksheets("sheet1"). Range("a65536").End(xlUp).Row
etypes = Worksheets("sheet1").Range("iv1").End(xlToLeft).Column
nextline = 2
For i = 2 To zonecodes
zcode = Worksheets("sheet1").Cells(i, 1).Value
For j = 2 To etypes
etype = Worksheets("sheet1").Cells(1, j).Value
enbr = Worksheets("sheet1").Cells(i, j).Value
Worksheets("sheet2").Cells(nextline, 1).Value = zcode
Worksheets("sheet2").Cells(nextline, 2).Value = etype
Worksheets("sheet2").Cells(nextline, 3).Value = enbr
nextline = nextline + 1
Next j
Next i
End Sub....................

View 2 Replies View Related

Public And Private Sub: Move To The Next Curios Level Of Understanding Macro And VB Coding

Jun 29, 2006

Where can I obtain some working examples or explanations as to what, where, when, why, and how these Public and Private Sub are correctly implemented? I see these code used extensively but not sure why it's there yet. A macro which uses a Private Sub is in one of my workbooks. Due the "progress" of things from here I now need to move to the next curios level of understanding Macro and VB coding.

View 3 Replies View Related

Understanding Code (code In Eglish Specially After The .Count /14-1)

Feb 6, 2009

Can someone explian to me this line of code in eglish specially after the .Count /14-1

View 2 Replies View Related

VLOOKUP Only Works On One Value?

Mar 25, 2014

some call databases from my internal telemarketing team, which are all on Excel databases. These contain multiple contacts within the same organisation, with no "unique identifier", i.e. there is no information specific to individual records EXCEPT their email address - and unfortunately, not every contact has an email address, which would prevent using VLOOKUP, which is the only function I can use to perform this type of lookup.

I have five separate spreadsheets, plus one master database spreadsheet. I have added five columns to the end of my master database, and I would like to use each column to identify which spreadsheet(s) the individual records appear in - normally this would be possible by using a VLOOKUP in each of the five new columns, selecting a unique identifier, and using the VLOOKUP function for each separate spreasheet. However, without a consistent unique identifier, I do not know another function which would allow me to use multiple identifying info (e.g. "FirstName" + "Surname" + "Company") to perform this task.

View 1 Replies View Related

Add New Sheet Only Works Once ()

Nov 30, 2006

If SheetExists(Sheets("data").Range("V" & x).Value) Then
'do Nothing
Else
Sheets.Add
ActiveSheet.Name = Sheets("Data").Range("V" & x).Value
End If
ProjectReview.Show

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

I'm trying to add a new sheet, if a sheet with that name doesn't exist already.
Which I have a feeling is pretty simple!. My Error is occurring at the * in the code. Basically I am adding a sheet per record. I'm using a unique identifier for each tab. The first run through, it creates a new sheet and opens a user form. When I close out that form, it returns to here and fails to work the second time through. I'm fairly confident it's because I'm using a select statement, I've also tried using it this way, but the set statement is invalid because it's not refrencing an object....

View 2 Replies View Related

Formula Works On One Tab But Not In Another

Oct 14, 2008

I want to do a vlookup between two tabs on a date in cell A1 eg 01-Apr-08
The lookup needs to be on month and year so I'm using

=Month(A1) which gives an answer 4 in cell A2
=Year(A1) which gives an answer 2008 in cell B2
I then oncatenate the results (=A2&B2) to get a unique reference 12008 - works ok

On the second tab, I've repeated the formula, I get an answer of 1 and 2008 but when I concatenate, I get a #VALUE!

I've tried
"text to columns"
unchecked Protect on the cells
worksheet is not protected

on the 1 and 2008 and concatenated result

View 9 Replies View Related

How AutoFilter Works

Jul 6, 2009

I need some information about How the auto filter in excel works?

Excel shows a drop box with list of unique values in that column, if i select one value only rows having that value are displayed.

Are other rows "hidden". I tried to unhide these rows but those are NOT hidden. What actually happens to rows that don't satisfy the filter criteria?

View 9 Replies View Related

Add-in UDF Works In New Workbooks But Not In Old Ones

Nov 24, 2009

I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.

I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.

Now I close everything.

I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.

View 9 Replies View Related

Vba Works In Windows But Not In Os X

Jan 16, 2007

I have put together the following code and it works fine on my PC. However, we are a mainly Mac based company and I nead it to work with a Mac. I am using the Actual ODBC Driver for Mac to give me a ODBC connection. I can do the query in excel but setting it up as a macro just will not work.

Sub Sales_Query()
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Range("B2").Select
Dim area As Variant
area = Range("B2")
With ActiveSheet.QueryTables.Add(Connection:= Array("ODBC;DSN=my_database;Description=My................................

View 2 Replies View Related

Macro Works Stepping Through But Not When Run On Its Own?

Jul 21, 2014

I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.

Here is the code:

VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy

[Code]....

I have two questions:
1. Why did this work in 2007 and not in 2013?

2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?

View 3 Replies View Related

PDF Macro Works With Windows But Not On Mac

May 18, 2014

I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.

This is the code:

[Code] .....

View 2 Replies View Related

Code Works In One Workbook But Not Another

Feb 19, 2009

This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?

Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub

View 2 Replies View Related

VB Works On One PC, Not On Another (Run-time Error 9 )

Apr 15, 2009

I run PCs at work and at home. Both machines run Windows XP and Office 2007

Home PC: Windows XP Home edition Version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

Work PC:Windows XP Professional version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.

View 4 Replies View Related

VLOOKUP Works In Some Cells But Not Others

Apr 13, 2006

Excel 2000

Really simple data sheet: list of names with homerooms
Lookup table: list of homerooms with teachers. (named "Range")

=VLOOKUP(E2,Range,2)

Some of the cells return the correct name, others #N/A.

As far as I can tell they are all formatted exactly the same; I've tried
several different formatting options. The correct ones stay the same; the no
data ones do not change either.

View 9 Replies View Related

Rounding Function Works On 1 PC But Not Another

Dec 1, 2008

I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).

I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".

I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.

I have attached a sample of the offending spreadsheet. The rounding function is in column "B"

View 10 Replies View Related

Formula Works On One Computer But Not Another

Nov 12, 2009

I have a odd problem. I have workbook that I made I placed this formula in cell A1

View 3 Replies View Related

Formula Works But Not For Formatting

Jan 23, 2012

I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.

The formula:

=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)"",INDIRECT("a"&ROW())="Item1")

View 5 Replies View Related

Forcing Fullscreen Works Sometimes?

Dec 6, 2013

I want to have a fullscreen application all the time, so for this I've added to things in my code:

1) Application.DisplayFullScreen = True in Workbook_Open

2) Same in WorkBook_WindowsResize

I open my workbook, it goes fullscreen. Until now all OK. However, if I press ESC or doubleclick on the topbar, it goes out of it.

Now the weird thing: if I click the "Restore window" button (which is right under the application maximize button that incidentally I have disabled) it snaps on fullscreen and THEN it keeps this state forever, even on ESC/doubleclick. Which is what I want.

So my question is: what does exiting from fullscreen and going back in change so that the workbook event starts triggering, and how I can replicate it programmatically? I've already tried WindowState = xlNormal followed by xlMaximize, it doesn't change anything.

View 3 Replies View Related

Formula Works On One Worksheet But Not The Other?

Jan 25, 2014

The formula I'm using is

Code:
=SUBSTITUTE(OFFSET(A10,-2,0),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,5),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,6))

But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.

In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.

[URL]

View 6 Replies View Related

Macro Works On One Sheet But Not The Other

Mar 31, 2014

In the following user defined function

Code:
Function CA2GT(ByVal S As String) As String
Dim x As Long, CA As Long, Total As Long
CA = InStr(1, S, "CA", vbTextCompare)
For x = CA To Len(S)
If Mid(S, x, 1) = "(" Then

'replace arguments: oldtext, start at number, number of characters, new text

[Code] .......

It works on one workbook but not another workbook. Why?

View 5 Replies View Related

IF Function Only Works With 7 Different Selections

Aug 26, 2002

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)

View 9 Replies View Related

Mod Function Works Wrong

Dec 28, 2006

I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.

And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.

So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1

But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6

View 9 Replies View Related

Step Through Works But Not When Activated

Sep 24, 2007

I have written this code to change the colour of a row of cells to bright green and to change the cell contents of cell(Row, 15) from “L” to “F”: This is the main workbook into which are pulled values from three others. I then want it to open one of the subsidiary workbooks, that feeds the information, to change the same job row to bright green and to change the cell contents of cell(Row, 15) from “L” to “F”. Then I want to save the changes and return to the main sheet: - The ActiveCell contains the Job Number.

Option Explicit
Public rw As Integer
Public Col As Integer
Public Job As String
Public JobNo As String
Public RowNo As Integer
Public wName As String
Public times As Integer
Public Pips As String
Public Nicolas As String
Public Cindys As String
Public wb

Sub FinishedinOfficeCindy()
.
rw = ActiveCell.Row
JobNo = ActiveCell
Col = ActiveCell.Column
If Col 1 Then
Check that the correct
MsgBox "Please choose the Job Number first"column is selected and
Exit SubThat the workbook is
End Ifthe correct one
If Cells(rw, 14) "Cindy" Then
MsgBox "Wrong Secretary Chosen - whoops!"
Exit Sub
End If

All works perfectly when I step through it but, when it is activated (Ctrl+Shift+C) it opens the other workbook and then seems to end? I even tried putting in delays to see if they would help but they didn’t.

View 9 Replies View Related

VLOOKUP Works In 2K3 And Breaks In 2K7

Oct 30, 2008

I have a file I inherited that uses many VLOOKUP formulas. It was written in Excel 2003, and works perfectly there.

However, I just had to install Excel 2007, and when I open this file, all those formulas return #VALUE! errors.

Is there a known issue with VLOOKUPs in 2007 versus 2003?

An example of one of the errored formulas is:

=VLOOKUP($C3,ItemMaster!$A$1:$N$5800,2)

It should be noted that the first row of this range is the column titles, rather than data. But as I said, the formula works fine in 2003.

What's completely bizarre is that if I edit the formula to either of the following, it works fine:

=VLOOKUP($C3,ItemMaster!$A$2:$N$5800,2)
=VLOOKUP($C3,ItemMaster!$A$1:$N$5800,2,FALSE)

Changing the first row of the range to row 2, or adding the FALSE at the end makes it work. But why would it work in 2003 and not in 2007?

This is an *enormous* file, and this is just one example of a *load* of formulas that are returning errors (so far, all seem to be related to VLOOKUPs - at least the ones I've found so far). It's 25MB worth of complicated formulas, with external links to Access databases for the source data, just to give you an idea of the scope. So going through and trying to find and then change every error that might be occurring just because 2007 doesn't like it is going to be a complete disaster. You never find them all. It might be simpler to just downgrade back to 2003!

View 9 Replies View Related

VB Script Works In Office Xp But Not 97

Jun 26, 2006

I create a vb script file using SAS that will open excel and create pivot tables. It works in XP but not in office 97. I am not a vb expert. Here is the code.

Set XL = CreateObject("Excel.Application")
XL.Visible=True
XL.Workbooks.OpenText "D:JDTEMPXLWORKBOOK.TXT", 437, 1, 1, -4142, _
False, False, False, False, False, True, "|", _
array( _
array(1, 2), _
array(2, 2), _
array(3, 1), _
array(4, 1), _
array(5, 2), _
array(6, 2), _
array(7, 2), _
array(8, 2), _
array(9, 2) _ ............................

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved