Program Not Picking Up Cell Value
May 3, 2007
I have a simple program that picks up an ID on sheet1, finds that ID on sheet2, picks up the information in one cell from sheet2, and brings that information back to sheet1.
The program works fine until for all IDs above row 570. After row 569 something strang happens. The programs is still able to find the ID but when it tries to pick up the one cell value it is saying it is blank yet you can physically see the data is present.
In all my years of VBA programming I have never encountered anything like this. Especially, where things work fine on half the worksheet and not on the other.
I am posting the code below, but I don't actually believe it is in the code. I cannot post the workbook because of the information it contains...
View 9 Replies
ADVERTISEMENT
Mar 26, 2013
I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix example [URL] Drop downs example [URL]
View 4 Replies
View Related
Mar 18, 2014
I have cell A1 and it has the following data "-110 or -99%".
I want a formula that will pick up the percentage numbers only, IE -99%. Please keep in mind that the percentages change.
It can be 10000%, 0%, -500000% etc.
Can there be a formula that will pick up whatever is before or from the right?
View 1 Replies
View Related
Jul 26, 2006
how to pick the numeric part out from a cell?
e.g. range("a1").value= 384HK
in range("b1"), i would like to have only 384, ignoring the HK
any excel tools or VBA code can accomplish this? there is a bunch of data that comes like this and i just dont have time to retype only the number one by one
View 4 Replies
View Related
Jul 29, 2006
I got some data like this
A1=CTAC US
A2=FCOB US
A3=TW US
The gap between each word and the word "US" is uneven. sometimes it's 3 spacebar apart, and sometimes 4.
I am trying the pick the part not including US. I tried =IF(ISERROR(SEARCH("US",A1)),A1,LEFT(A1,SEARCH("US",A1)-1)). It works but when I use match function to look up the word. It returns N.A. As the output is not just CTAC(4 characters), but 5 characters(including blank). The problem is the gap between between the word and "US" is different for each cell. so I cannot just minus the same character in the above formula (e.g. -2 if there is one blank cell). Anyone can tell me how to pick out the word and with only the number of characters in the word?(no blank)
View 5 Replies
View Related
Dec 17, 2007
ending of this to get the value of a cell that is on the the same line(volatile) but in column BA
TextBox1.Value = ActiveWorkbook.Sheets(ComboBox7.Text).Range(ComboBox6.Value)
Where combobox6.value is the row number that I want but column BA is the cell
View 9 Replies
View Related
Jun 2, 2009
Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"
View 5 Replies
View Related
Jun 8, 2006
This is the command I want to run from a cell:
c:windowssystem32mstsc.exe /v :<servername>
This will allow me to click on a servername in a column and RDC into it.
View 9 Replies
View Related
Jun 3, 2009
Trying to program cell C1:
If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection
etc.
There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?
View 6 Replies
View Related
Jan 10, 2008
The thing is that i have alot of sheets (more than 20) and my program picks 2 values from the same cell locations on each sheet. This leeds to more than 30 arguments in my formula. I am gonna use this for alot of excelfiles constructed in the same way, but with varying order and names on the sheets.
View 9 Replies
View Related
Apr 2, 2014
The purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".
The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.
I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.
View 5 Replies
View Related
Oct 6, 2008
I work in school which for example offers 20 courses for every student. Therefore I have a spreadsheet which has students name in A and B then the 20 courses spread from C to V.
Each student will study 3, 4 or 5 of the courses and will have value in the corresponding column (their teaching group) the rest of their row will be blank.
For each student create in columns X, Y , Z, AA and AB I would like to be able to pull from the 20 courses those which the student studies. Therefore finishing up with C to V hidden and just the student names and the 3, 4 or 5 courses they study.
Any ideas? if one method is to get them into one cell how do I get them into the 5 columns; i know DATA : Text to Columns but won't have anything to Delimit it with and can't use Fixed width as students will study subjects with different length charactors.
View 6 Replies
View Related
May 20, 2014
I have a table, which has pairs, e.g like this:
A=1
B=2
etc
how to give the value to variable based on this pairs? I mean something like this:
If left(mystring;1)=A, then
myvar=1
I used the case-structure, but maybe there is a better solution, which can use an array in order not to write all of the items whenever I need it.
View 9 Replies
View Related
Oct 21, 2009
I've created a useform for recording stock trades. So it asks the user to enter values for
"Date of trade"
"Time of trade"
"Name of stock"
"Price"
Right now I'm using a calendar control I downloaded from
http://www.fontstuff.com/vba/vbatut07.htm and it looks good, but I'd also like to have an easy way for the user to select the time of the trade (in minutes).
Is there an easy way to have a time picker? Like a dropdown box maybe? I'd like to have date and time picked separately since the date will remain the same for a lot of trades but the time will always change.
I've googled it a lot and some people mentioned using the datetimepicker Access add-in but I'm not sure if that's right for me. Also, I'm using Excel 2007 but the form will also be used on excel 2003 machines, will that affect the choice of solution?
View 3 Replies
View Related
Jan 5, 2012
I have a CSV file which contains a list of records about offices. This CSV file is generated from another program.
I read the CSV file with VBA, a SELECT * SQL statement which puts the elements into an Office Object and adds the offices to a collection.
For some reason, when one of the Telephone numbers is read it is being identified as a Null value. If I manually open the CSV file and change it to a different number e.g. 12345 and try re-read the CSV file with SQL VBA the number gets read fine. Every other element in the CSV file reads fine except for this. The number itself is 7736786460.
Attached is the small snippets of code to read the CSV file which is fairly straightforward.
Code:
Sub LoadUnits()
Dim oRs As Recordset
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
[code]...
View 7 Replies
View Related
Jan 5, 2012
Copied the below formula 1 column to the left - to pick up numbers 1 column to the left:
=IF(SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7)=0,"",SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7))
But the below resulting formula still picks up the same columns as the formula above:
=IF(SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7)=0,"",SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7))
View 1 Replies
View Related
Apr 10, 2014
I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:
Code:
=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]
Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1
LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)
... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.
I don't get an error, but it's not adding anything up, either.
View 9 Replies
View Related
Sep 20, 2007
I have a excel file with names of employees and projects handled by them. In the 1st column is the name of employee and in the second column, its the name of the project. Every employee has approx 10-15 projects made by them.
I have another file with name of all employees in the 1st column. What is want is to insert the name of any two projects (taken randomly from 1st file) ahead of each employee.
View 9 Replies
View Related
Feb 4, 2008
I have Workbook with numerous worksheets,on one particular worksheet i require a formula/macro code that will pick up (say) those cells highlighted in red and then transfer that data set and the other selected information on that row to a another worksheet within the workbook.
E.g
Sheet 1 contains columns A to J;
Sheet 2 is where i require the information which is highlighted in red to be uplifted and pasted too.
so if Cell B2 contains the highlighted cell how would i uplift and transfer this along with data in cell C2,H2,I2 and J2 automatically to sheet 2.
View 9 Replies
View Related
Apr 26, 2008
I have a question that has been bugging me for a long time and an answer to it can be a big timesaver for alot of excel users specially accountants that do account reconciliations..
lets assume that I have 3000 numbers(amounts) listed on an Excel spreadsheet vertically, and i know for a fact that the sum of most of these 3000 transactions equal a certian amount..lets say $1,200,320.23,
lets also assume that all these amounts on the list are unique..
now..I want to isolate these numbers(amounts) that add up to that $1,200,320.23 by way of highlighting them or any other method. once that is done, I would be left with the amounts that are causing the descrepency in that account. I wonder if there is any function, utility or even VBA code in excel or even another software that can help solve that problem..I hope my explaination was suffecient, let me know if you need further clarification.
View 9 Replies
View Related
Aug 1, 2008
I'm using the following formula to pick up data from another worksheet
=SUMPRODUCT(--(RawActuals!$A$1:$A$10000="jun"),--(RawActuals!$E$1:$E$10000=A236),RawActuals!$M$1:$M$10000)
It's picking up most of the data, but it's missing some - and it's to do with formatting of SOME of the data within the RawActuals sheet
212605Agency Staff - Professional & Other212701Staff Cost Tfr to Change Programme221112Property Rents - Passing Rent221310Hire, office/courtr'm/related eqpt
The 1st, 3rd and 4th lines are correctly picking up the data - but the 2nd is not. They are all set to the same number format.
View 9 Replies
View Related
May 18, 2009
I have a column where some cells are coloured yellow. In another column, I'd like to mark out these cells, e.g. if the cell next to it was yellow, I would like to to display 'Y'.
View 9 Replies
View Related
Oct 22, 2013
I have a long table like this:
Car,date last washed
Civic, 11/1/2012
F150, 5/4/2013
Corvette, 7/3/2013
If I washed the Corvette, I'd add another row to it:
Corvette, 10/22/2013
And I would like to remove the "Corvette, 7/3/2013" entry. What's the easiest (read: least user intervention) way of doing this?
View 2 Replies
View Related
Nov 7, 2013
I have a table like this:
Book, Loaned to, Date
A Tale of 2 Cities, John, 1/1/2010
Oliver Twist, Mary, 2/3/2011
Great Expectations, James, 4/6/2011
Oliver Twist, Greg, 6/2/2010
I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:
Book= Oliver Twist
Loaned to= Greg
Date= 6/2/2010
View 2 Replies
View Related
May 5, 2009
This should be a simple one, But I can get it to work. Could someone advise me as to how I can change this line of code to pick sheet(2) as it is now it picks sheet(1) . and throws the formula off. In A nut shell the formula is looking at sheets(1) C3 When it needs to look at sheets(2) C3. I always get messed up with the & and "" and!
View 3 Replies
View Related
Mar 14, 2003
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies
View Related
Dec 21, 2011
I am trying to write a macro that will automate the process of selecting rows from a data sheet. I want the rows to be selected based on the values in two columns. I want to find and select the row at the beginning and end of positions in a column where the values are equal. For a quick example, if my data looks like this below, I'd want to select the entire row for rows 2,3, and 6... I also need the row just before the value in both columns are equal.
row column11 column12
1 0 1
2 0 1
3 1 1
4 1 1
5 1 1
6 1 1
7 0 1
8 0 1
I have written this macro and it is not working... are there any obvious problems with this? This is new to me and I'm not exactly sure that I've used the correct commands. It also seems like LastRow is referencing 0 and not 12965 or whatever the last row of filled data is for the sheet.
Sub Deltadata()
Dim RowNum As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Finds last filled row (should be 12965)
For RowNum = 3 To LastRow Step 1
[Code] ........
View 4 Replies
View Related
Jul 22, 2006
I need to look at each filename in a particular folder, open the file if the name contains "THO", then modify the file and close it. I know how to open, modify, and close files using VBA. I'm stuck on how to pick one particular file based on the filename. I copied the code below from another thread. It's great for opening all files in the specified directory. Can anyone show me how to modify it so I only open files with "THO" in the name?
Sub OpenWorkbooks()
Dim wb As Workbook
Dim dirr As String
Dim I As Long
Application.DisplayAlerts = False
dirr = "C:Documents and SettingsHalldaviDesktopKoen's weekly sample reports"
With Application.FileSearch
.NewSearch
.LookIn = dirr
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Execute
For I = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(I))
Next I
End With
End Sub
View 3 Replies
View Related
Sep 11, 2012
I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.
What I have so far is;
If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120"
End Sub
A B C D E F G H I J K L
1
2
4
5
6 Panel plate 24 by 21 <1.59 N/A
7
8
9
10
(Doesn't seem to work)...
If those four cell match up then i need I6-L6 to line up with whats required.
If they don't equal that i need it to stay clear.
I also need to add the same macro with different options.
View 7 Replies
View Related
Oct 13, 2013
last item in we 13-9-2013 is 13-9-2013
last item in we 20-9-2013 is 13-18-2013
last item in we 4-11-2013 is 30-9-2013 as shown in the attached
1.jpg
View 2 Replies
View Related
