When I have a text field, such as 123 Main Street, Suite 456 and I copy it down, I get
123 Main Street, Suite 456
123 Main Street, Suite 457
123 Main Street, Suite 458
What is this feature called, and how do I turn it off?
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
How can I fill downwards the following formula?
=(SUM(B$2:B13)*0.05)*(J2/12)
=(SUM(B$2:B12)*0.05)*(J3/12)
=(SUM(B$2:B11)*0.05)*(J4/12)
I can't seem to get the fill handle "+" sign to give the correct amounts when
I want to copy a formula down a column. It seems to skip every 2nd row with
the wrong amount or sometimes it will give a 0.00 sign.
I have a function =TimeSinceIns(3) in cell K3. Where the 3 represents the row number. I would like to fill the function all the way down to cell k100. And be able to do this reguarly. Unfortunately when I use the fill handle it leaves 3 as 3 when I want it to be changing to the appropriate row no. Ie in cell k4 would be =TimeSinceIns(4) and so on.
View 3 Replies View RelatedI would like to use the Fill Handle to create a column of dates.
The dates must be formatted like this: DD/MM/YYYY HH:MM
The Fill Handle completes the series, but not the way I want.
I want a series that changes the day only. So:
02/02/2012 09:00
03/02/2012 09:00
04/02/2012 09:00
For whatever reason, Excel currently adds a minute or two to the minutes and does not increase the date.
13/2/2012 09:20
14/2/2012 09:20
15/2/2012 09:20
13/2/2012 09:21
[Code] .......
I am trying to copy a countif formula down a column using the fill handle, but when I copy the formatting down the column the range changes. Is there a way to make it so that only the variable changes and the range stays the same?
View 2 Replies View RelatedI've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.
The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?
'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False ....................................
b2 = a2 * 150
c2 = a2 * 145
d2 = a2 * 140
e2 = a2 * 135
[Code].....
in the first row i want to use the fill handle at 135 and drag rightwards so as to copy/retain the pattern (decrementing by 5, from multiplier 150 until 100)
how would I achieve that?
p.s I also want the formula for columns to work with the fill handle drag downwards.
I have items in a column like below:
50
50
50
50
What I would like to be able to do is highlight the four 50's and fill down, but I would like for it to increment and paste the next four filled like this:
51
51
51
51
Then below that the next four filled like this:
52
52
52
52
If its a macro that'll be required I'd like to to be able to change the number of rows to be repeated (4 in this case, but could change).
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
I have data in several rows whereby my column D looks like this:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Throughout the week, additional data is added to the rows, thus creating blank cells within this D Column:
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_04.0000
FebtReport14_05.0000
Is there a macro i can run which will re increment only the blank cells to look something like this?"
FebtReport14_01.0000
FebtReport14_02.0000
FebtReport14_03.0000
FebtReport14_03.1000
FebtReport14_04.0000
FebtReport14_04.1000
FebtReport14_04.2000
FebtReport14_04.3000
FebtReport14_05.0000
Also , I will be adding new data quite often, in which newer rows may need to be added in between: ie:
from this
FebtReport14_04.0000
FebtReport14_04.1000
to something like this:
FebtReport14_04.0000
FebtReport14_04.0100
FebtReport14_04.0200
FebtReport14_04.1000
I have been frustrated trying figure out what I would assume is actually a pretty simple solution. All I am trying to do is grab a set of cells and repeat them down the column a set number of times. The 2 forms of this are discribed below.
A: I have a set of numbers from A1 to A51 I want to repeate those numbers down the columns 84 times.
B: In addition to this I need to have the cells from B1 to B51 Repeat 84 times but with an increment of 200 each time. So I would have say 1 in B1 - B51 and in B52 - B102 it would turn into 201 and so on and so forth.
I have items in a column like below:
Line (A1) asdfasdf
Line (A2) adsfadsf
Line (A3) asdfasdf
What I would like to be able to do is highlight the three columns and fill down, but I would like for it to increment and paste the next three lines filled like this:
Line (A2) asdfasdf
Line (A3) adsfadsf
Line (A4) asdfasdf
Then below that the next three lines filled like this:
Line (A3) asdfasdf
Line (A4) adsfadsf
Line (A5) asdfasdf
The (A1, A2, etc.) are cell ranges and already contain the concatenate function that pulls data from a different location. The problem I am having is when I
highlight and fill down, it doesn't increment the cell by +1 (Ex. A1 beomes A2) but instead by +3 (A1 becomes A4) the number of lines highlighted.
I have got the following problem. I have got a table which looks like this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
When I mark the selected cells and scroll down using the autofill option I get this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
=Time!A6
=Open!A6
=max!A6
=min!A6
=Close!A6
Why does it give A6? I want A2 instead of A6 because I want to create a table from A1 to A500 but without these jumps from A1 to A6.
Pre-requisite: I would consider myself to be very poor with excel, based on what I've read on this forum and found on my web-searches. I have a worksheet that has a list of data on the left going vertically, then a summary of this data going horizontally across the top. It is not arranged in such a way that transposing the data will do what I want. I am pulling the 5th word out of the title of each block of the vertical data and need to show this word on the horizontal section.
When I use this formula to pull the 5th word: =MID(MID(MID(SUBSTITUTE(A2," ","^",4),1,256), FIND("^",SUBSTITUTE(A2," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A2," ","^",4),1,256),FIND("^",SUBSTITUTE(A2," ","^",4)),256))-2)
I need to increase A2 to A30, then A58 (up by 28 every time) in every instance in that formula. The fill handle increases the values by 1, instead of 28 (even if I do 3 or 4 instances manually) How do I do this? I've run into this problem in other scenarios, and there HAS to be a way to get around it.
I have a workbook which is set up to take an average heart rate of a participant from a series of data points. I have set the spreadsheet up before I have collected some of the data. (so I can review the project at the 3 months period and its an ongoing project).
The problem is that if there is no data in a participants column then excel correctly gives you readout of “#DIV//0!”. On my results page this #DIV//0!” makes it hard to read the spreadsheet. Is it possible to get excel to turn #DIV//0!” to “0” or even turn it to a blank cell?
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.
View 4 Replies View RelatedIs there a way to type in a cell and click on a Command Button instead of the Enter Key-- and then run a macro using the value that was typed into that cell? Or any other way to eliminate the need to hit Enter Key to record the user input and then hit Command Button to run the macro? I have 40+ users who login in by selecting their name from a drop down list and then entering their password in another cell. (what they input must match another cell in same row.) Attached is scaled down model of Workbook and the macro that works to login.
View 5 Replies View RelatedI'm trying to manipulate a URL in an already opened IE browser window. In a nutshell, I need to change "http://www.excelforum.com" to "http://www.excelforum.com/this-is-the-new-part"
The code prior to this does the following:
- Opens a new instance of IE
- Enters the username and password required to get into the site
- Scrapes the source code, imports it into Excel, and parses the data
- Finds a row based on the current date and some text
- Extracts from that row a unique identifier...
- Obtains the window handle ID for the instance of IE displaying the website
Now I need to add the unique identifier to the url. I cannot open it in a new tab or instance of the browser, as it revokes back to the login screen. I was assuming that the best way to do this would be to get the window handle, then just send a "tab" to get back to the URL, and enter the new URL. If there's any other way to do this, I'm all ears. Otherwise, how can I send the keys using the window handle?
Would need your expertise to advice on how to create a formula to handle the following data in order to produce the desired output below.
I have some data in Sheet 1 and the corresponding data in Sheet2. The output is in Output Sheet.
If the data in Sheet1, "Student1" match the data in Sheet2, "Student1", then it will copy all the entries for Student1 in other worksheet with the header "Student1" (Output Sheet).
The same thing goes to other Student in the list. For this sample, I only provide a short list of data in Sheet2, it can be long, more than 5 students.
Sheet1
------------
Student1 | Student2 | Student3 | Student4 | Student5
Sheet2
------------
Student2 | Student5 | Student3 | Student4 | Student1
aaa | aaa | fff | aaa | aaa
ddd | fff | ccc | ccc | bbb
eee | ccc | ccc | ddd | ccc
ccc | ddd | ddd | eee | ddd
eee | eee | eee | bbb | eee
bbb | bbb | aaa | fff | fff..................
I have this on sheet1 on B2 and B3 , where war-200 is chosen through drop down menu , while another sheet named pics has war-200 on A1 and its picture on b1 , is there a formula to throw the picture on C3 of sheet1 from table of code and pictures
Select Code
WAR - 200
The code handles keydown, keypress events with no problem. Also similar change events, and probably more.
This code is at UF initialize event:
Option Explicit
Dim TextBoxes() As New Class1
Dim ComboBoxes() As New Class1
Private Sub UserForm_Initialize()
Dim TCount As Long, CCount As Long, c As Control
TCount = 0
CCount = 0
'Set TabIndexCollection = New Collection
For Each c In Me.Controls
' TabIndexCollection.Add c, CStr(c.TabIndex)
If TypeOf c Is MSForms.TextBox Then................
How can i use the Autofill handle for letters?
I want to be able to do the following:
In cell A1 i type: A
In cell A2 i type: B
Then i want to use the handle to get in column A the rest like C D E....Z AA AB AC.......AZ BA BB BC etc.
I have below codes:
Private Sub Worksheet_Calculate()
For Each rCell In Range("D2:CU5")
Select Case rCell.Value
Case 1, 0.000000001 To 89.499999999
rCell.Interior.ColorIndex = 3
Case 2, 89.5 To 99.499999999
rCell.Interior.ColorIndex = 6
Case 3, 99.5 To 109.499999999
rCell.Interior.ColorIndex = 4
Case 4, 109.5 To 999
rCell.Interior.ColorIndex = 8
Case Else
rCell.Interior.ColorIndex = 15
End Select
Next rCell
End Sub
However if a value within the range "D2:CU5" happens to be #Div/0! (or any error value for that matter) I get a debug error message. Then the CASE ELSE formatting doesn't apply. I expect that everything else should fall under CASE ELSE. Is there a way to handle this i.e. the formatting under CASE ELSE applies?
How do I get the handle for a combobox on a spreadsheet? I have tried ChildWindowFromPoint and RealChildWindowFromPoint without any luck.
View 9 Replies View RelatedI am doing a vlookup accross two sheets where I am adding up the first Vlookup with the second vlookup (ie vlookup sheet 1 + vlookup sheet2) and in one of the sheets the vlookup formula finds a value but in the other sheet it finds nothing. As such when the two formulas are added together it gives a value of #N/A. Since I have made a macro for this it is annoying.
The macro is as follows:
Sub Vlookup()
Dim rowc As Long
Dim i As Long
Application. ScreenUpdating = False
rowc = Application.WorksheetFunction. CountA( Range("a:a"))
For i = 2 To rowc
Cells(i, 3) = "=-VLOOKUP(RC[-2],Hksaldo!C[-2]:C[3],6,FALSE)+VLOOKUP(RC[-2],Likv!C[-2]:C[2],5,FALSE)"
Next i
End Sub
how I can get around this. Ie when I have a non applicable value i one of the Vlookup formula the formula should only look at the vlookup with a value?
I have the following formula in a cell C6 : "='Data Dump'!G20". I want the adjacent cell on the right (C7) to be "='Data Dump'!G21" and C8 to be "='Data Dump'!G22" and on and on. I'm trying to use the Auto complete handle on the bottom right hand corner of the cell (which looks like a + when the cursor is over it) to drag the formula so that the "Data Dump - G" values increase as I go (g21, g22, g23 and onwards). However, rather than the numerical part of the cell incrementing, the character (letter) does so instead, so I get H20, I20, J20 and onwards.
View 2 Replies View RelatedI use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
Code:
=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.
I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.I am having 2 problems:
1- The code loops only once instead of once for each open workbook !
2- The Msgbox displays an empty string instead of the workbook caption !
If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function
Code:
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long[code]......