I have a column of numbers, for simplcity lets say they are 1,2,3,4,5. The numbers change automatically, so the column can look like 1,1,1,1,1 or 1,5,3,3,2 etc. I would like to know which command can monitor this column and return a true statement if two or more of those numbers match the number I specify.
View 3 RepliesI need to use SQL decode functionality in Excel. I tried but Excel won't accept decode. Is there a way to get the same effect in Excel?
Example: quite often, people use letters to represent numbers, like 1234D = 123.44, 5432G=543.27 in text files. To converter from letters to numbers, I can use decode (substr(A1,5,1), 'A', 1, 'B',2,'C',3,......,'{', 0, null).
Not sure how Excel handles this?
I have a standard multi-page excel workbook in Excel 2013. I have built a user form that will allow for user input into my main excel page. This inpu includes many different variables, including one called "Paint". The value of paint can currently be selected from a drop down box that pulls its values from a spreadsheet in the workbook (Paint!A$1,A$50) but here's the issue. I have over 50 possible values for paint and it makes the drop box time searching for the right one kinda hard.
Here's what I want to do: Have a button beside the paint drop-down that says "Search", When the users clicks it it opens a search dialogue of some kind that will return the closest values to their search. When they click the value they want it gets entered as the selected value for the "Paint" drop-down box.
Here's my questions:
-Is this even possible ?I know enough about VBA to know that this is probably difficult in which case id still like to try.
-If it is possible. Are there perhaps any places you could point me to that would have some sort of starting point for me to work from. Remember I got the other part already I just need the search functionality.
I have sheet1 along with three columns as below
Result column will show the result after sum ** column A & B
[Code] ......
This is simple that i will add this formula =A2+B2 in Result column cell C2.
But i want to perform this addition action on Sheet2 and return the result in column C2 ** Sheet1.
What formula should be populate in Result column ** Sheet1 that could catch addition formula from Sheet2 dynamically and display the result ** column A & B in Sheet1 .
And what type ** formula should be use in Sheet2 to add two columns dynamically.
The same concept that we are using in any functions like as below:
Sub Function Plus (value1 As Double, value2 As Double)
Plus = (value1 + value)
End Sub
One of the useful functionality was the Excel-Addins , now I would be glad if someone could help me by posting the latest Addin or Analysis tool-pack. A setup file would be of great help as some of my formulae are suing such functions which ach are not provided by the reguale EXCEL such as "QUOTIENT" and "NETWORKDAYS"..
There's just one more request , I had a very good font by the name "POOR RICHARD" in one of my files, however after formatting and re-installning I do not have it anymore.
By typing either first letter or first two letters in cell A2 the list box should bring up matching street names. Also listbox should manually allow to select required street name by scrolling down.
View 3 Replies View RelatedEach week I get new weekly data-metrics. I have a series of formulas that figure LAST 4 weeks average, LAST 8 weeks, etc etc. How do I drop in a new week of data and have my formulas adjust? For instance, my formula figures last 4 weeks average of May Wk 1, May Wk 2, May Wk3, May Wk4. Now it is June Wk1, so I want to figure (without changing any formula) May Wk 2, May Wk3, May Wk 4, June Wk 1.
View 1 Replies View RelatedI have a hyperlink in a cell that is put there by copying and pasting it from an e-mail message. The hyperlink has a nice friendly name that is not the actual URL. Let's say that the hyperlink is in Sheet 1 A1.
In Sheet 2 I have a cell that says C3 = HYPERLINK('Sheet1'!A1), however this doesn't import the URL with it. All it does is make a hyperlink with the nice friendly name that goes nowhere.
How can I reference Sheet 1 A1 and have the result in Sheet 2 C3 be a clickable URL that works exactly like the original that is sitting in Sheet 1 A1?
I have tried the following code :
Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
[Code]...
However it does not work for me. All it does is deliver NAME# as a hyperlink which cannot be clicked. I am very new to putting code into Excel so it is possible that I do not understand how to make a user-defined function work. I don't even understand how the code itself works.
I am looking for a solution by a macro or a formula to get the number of occurance of a particular word in all the worksheets of a xls.
View 4 Replies View RelatedI feel like I have lost some functionality due to merged cells. For a worksheet change event I have the following:
ElseIf Target.Value = "" Then ... blah blah blah
For a normal cell, every time a cell is deleted (in the appropriate target zone) then it runs the code I have in the subsequent lines
Except it doesn't work for merged cells, unless I manually go into the cell, backspace all of its contents and then press enter
Is it possible that if i press tab in excel my active cell offsets by a row and two negative columns.
View 1 Replies View RelatedI'm looking for the functionality in Excel where you can unhide/hide a group of columns or rows by clicking the plus or minus sign below the ribbon but above the spreadsheet itself. I forget how to do it, but it basically groups the columns together and hides or unhides all of them with one click.
View 3 Replies View RelatedI've attached an excel invoice template I've been making for our company. I'd like to protect all the cells except A12:H42, C8, B9, G4 and I44. The problem is, if I protect the worksheet (with these cells unlocked) then it won't let me run the macro (for inserting new rows for extra products).
View 5 Replies View RelatedI would like to select a item from a list and have a text box display data from the next column (corresponding row) Also, I would love if I could type something into a second textbox and have that copy onto a third column (again, corresponding row) Also, if the add comment command button could transfer that record to the "commentted items' sheet. I have attached an example.
View 2 Replies View RelatedI am presently designing a feature in excel through macros where I have created a box and named it box1 on sheet1 and when I click on the box1, then I want to go to sheet2 in the same workbook to specific row automatically.
View 7 Replies View RelatedI am currently using a bunch of msgboxes to tell the user if a specific error has occurred. I want to get rid of the ugly gray box and customize my own. I am having problems setting up the code as I would like it to function like msgbox does where I can supply a header and message string and then it displays in the userform. I tried coding it kind of how I would pass variables to a function and it doesn't seem to work.
Userform:
Code:
Private Sub userform_initialize(msg As String, hdr As String)
MessageBox.Caption = msg
myMessageBox.Caption = hdr
End Sub
Sub:
Code:
Call myMessageBox("Hi", "hi")
I have a spreadsheet with macros and conditional formatting assigned to it. What I want is to be able to copy only the displayed contents, without the macros and formatting instruction, to a separate workbook for reporting purposes.
View 2 Replies View RelatedI've got a data dump that must be edited/converted prior to pushing it into a different main frame system. There can be NO decimals in the end result. Here's the challenge:
______Col A____Col B___Col C___Col D____Col E
Row1__TOT_____1Q_______2Q_____3Q______4Q_____
Row2__20.00____5.00_____5.00____5.00____5.00___
Row3__26.00____6.50_____6.50____6.50____6.50___
Row4___0.00____0.00_____0.00____0.00____0.00___
Row5_139.00___34.75____34.75___34.75___34.75___
Row6__13.00____3.25_____3.25____3.25____3.25___
Starting on Row 2, there's a break down of 4 quarters of data divisible by 4 using the whole number TOTAL of Parts in Col A.
The analyst must first break it down into 4 parts (as done in Col B through E). Then, round up and down appropriately to ensure that NO DECIMAL data is entered into another main frame system.
Can't copy one formula downward because if it ends in .50, the up/down sequence is diff from how it would be rounded if it ended with .75.
Need vba to be smart enough to evaluate "IF" and apply "THEN" according to what it sees in Col B's data and convert B, C, D and E appropriately. (I will tie this to a button within a toolbar to run the macro/vba when the conversion of B thru E is needed.
EXAMPLE:
IF B's data ends in: xx.00 or xx.50, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDUP, ROUNDdown, (this one doesnt really matter but works)
IF B's data ends: xx.25, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDdown, ROUNDdown,
IF B's data ends: xx.75, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDUP, ROUNDUP, ROUNDdown,
Using ROW 6 as an example for how to handle xx.25's:
B6 would convert up to 4
C6 would convert down to 3
D6 would convert down to 3
E6 would convert down to 3
TOTAL after conversion = 13
how to create a simple form that would search a sheet for a word and display the list of resulting rows - something similar to Adobe Reader 7 Search functionality ?
If I was having administrator privileges on this PC, I would simply install Cygnus and use its "grep" command on a CSV file but without that possibility, it's rather cumbersome to search an Excel sheet when you need to do it repetitively, and I need to locate a specific row each time without knowing the exact content of the cell (only searching for one or another word I know the cell contains).
I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code:
Option Explicit
On Error Resume Next
ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
I've always found these forums a valuable resource while searching for answers to coding questions. My question today deals with international distribution of Macros.
I've written a macro and its been distributed worldwide. It works just fine on the multiple instances where people have installed it here in the US. However, two of my colleagues in Mexico and Brazil are recieivng Type Mismatch Errors. Here is the code that is crashing:
'Making sure I'm on the right page
Sheets("Summary").Activate
'resetting variables to the top of the page
Let intsummrow = 7
Let intsummcol = 1
' Loop to find the last column
Let vartemp1 = Cells(intsummrow, intsummcol).Value
Do Until vartemp1 = ""
Let intsummcol = intsummcol + 1
Let vartemp1 = Cells(intsummrow, intsummcol).Value
Loop
The precise type mismatch is occuring at:
Do Until vartemp1 = ""
vartemp1 is a variant variable which at this point is storing the cell contents
intsummcol is an integer variable storing the column number
intsummrow is an integer variable storing the row number
And through my follow-up testing I've confirmed that in my US based copy cell A7 does have text in it.
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
Is it possible to edit my .bat file using vba?
View 1 Replies View RelatedI am using Excel 2000
I have recently opened a file sent to me, and since closing it my Edit menu does not work properly when I open any existing files or start a new file
The problem is with Copy, Cut Paste and Paste Special, when I click on the Edit menu they are all greyed out, also if I right click they are also greyed out
Obviously the file I opened has changed some settings, how do I re-set this
Is there a way to setup a spreadsheet so data that has been entered cannot ne deleted or edited, and only the last field in the column can be accessible? So if I have info entered in a1, a2, a3, b1, b2, b3, no one can edit or delete them, and can only add to a4 and b4? Will I need to write a macro for this or is it something I can do with certain settings?
View 9 Replies View RelatedThe edit box used to make or edit ranges for Defined Names is VERY difficult to use for long char strings. The arrow keys do not move the cursor within the edit box & the end key does not work.
How to edit range names that are 100-200 characters long?
Are there settings to alter behavior of arrow keys withing these "range definition boxes"?
I have an autoshape with a text box in it. It is one of those "scroll" shapes. I want to be able to edit the text in the box with VBA, but I don't know how to call it because I can't seem to find the properties for it. I tried recording a macro to do it, and it called it:
View 5 Replies View Relatedhow to set up and/or edit my macros so that I can use them in various outside workbooks without having to open the specific workbook I put the actual macros in?
View 2 Replies View RelatedWhen I list my macros using alt+F8 the edit option is no longer available, any ideas what I have done?
View 9 Replies View Relatedhow can I open a .txt file with vba and replace alll "," with ";" ?