Writing Values To A Blank Cell
Aug 8, 2006
I have a combobox that the user selects a value from. Depending on what value is selected i want to write three different numbers to three different cells. I pretty much need to know how you get VBA to put a number in a cell. The numbers are nowhere on this sheet. Just in the code.
Case "1"
Sheet1.Range("M31").Activate
ActiveCell.Value = "1"
Sheet1.Range("O31").Activate
ActiveCell.Value = 0
Sheet1.Range("Q31").Activate
ActiveCell.Value = 0
Case "2"
Sheet1.Range("M31").Activate
ActiveCell.Value = 2
Sheet1.Range("O31").Activate
ActiveCell.Value = 0
View 8 Replies
ADVERTISEMENT
Dec 11, 2008
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
View 8 Replies
View Related
Jan 2, 2009
writing values to range - first entry repeated
I'm having trouble with this set of commands
View 4 Replies
View Related
Oct 22, 2009
The below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?
View 2 Replies
View Related
Jan 21, 2010
I have a problem, basically im writing in the cell but it keep going along, i want it so when i press enter it creates a new line in the cell, so i can have two lines of text in one cell. do i have to do something in the text settings?
View 2 Replies
View Related
Apr 8, 2014
I have a reference written as a string in cell "AS4", the string is results!A4
I want to write that same reference in cell "as5". I have written the following
[Code] .....
However, cell "as5" returns the string as =results!'A4', with single quotes around A4
Therefore the reference isn't working.
View 2 Replies
View Related
Apr 15, 2009
I'm working on creating an array of letters. I'm then writing that array into a cell. The following code is for writing the letters "ABC" into a cell with an array.
View 4 Replies
View Related
Jul 20, 2006
Until now, I have been using the terrifically inefficient way of writing in a formula in a cell and copy pasting the value in my VBA
Easy example (concatenating):
Sheets(Sheet2).Select
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=""Data collected on ""&TEXT(NOW(),""dd/mm/yyyy"")&"" at ""&TEXT(NOW(),""hh:mm"")"
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
More complex example (with IF statement):
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-3],13)=""No list found"",""Data requested for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& "" out of range"",""Data downloaded for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& """")"Range("D2").Select...............
View 4 Replies
View Related
May 30, 2014
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
[Code] .....
View 2 Replies
View Related
Jul 25, 2014
I am designing a spreadsheet for my colleagues and I have run into a problem. Range U16 tu U and the first blank row has values TRUE or FALSE. The problem is you will never know if it's U16:U21 or U16:U90. The thing is to come up with a formula (no vba) that will count all TRUE values in that range. I found a formula on the internet to find the first blank column: =MATCH(TRUE,INDEX(ISBLANK(U16:U300),0,0),0) + 16 - 1
The formula works great and if I put it in, say, cell P2 it will return the correct result.
Now, the cell K2 has the following formula: =COUNTIF(U16:U300,TRUE). It Counts incorrectly. I do not want it to run to U300 but to whatever P2 returns. How can I do this?
View 9 Replies
View Related
Sep 30, 2006
I got 3 columns of cell values as follows:
Demo 1
COL A COL B COL C COL D
_ 12 14 16
_ 32 12 18
_ EA LW RA
Demo 2
COL A COL B COL C COL D
12 12 14 16
32 32 12 18
EA EA LW RA
14
12
LW
16
18
RA
Can data of the 3 columns be copies (by vba code) to the blank col A in the exact sequence as shown in Demo 2?
View 2 Replies
View Related
Aug 17, 2007
I have a cell in which I use the following formula:
View 13 Replies
View Related
Jun 16, 2013
Process is that I read in lines from a text file and then based on a filter I then write some those values to a sheet. One of the values I transfer is always 5 characters with a trailing space. The first four may be all numbers (e.g. "0013 ") or a mix (e.g. "013G "). When I write these to the spreadsheet the ones without letters always end up as a number (e.g. "0013 " become 13). How do I force the cell to show all values as text in the cell just like I read it in. In fact, I'd prefer stripping out the trailing space but it's not critical. That I can do (TRIM).
View 4 Replies
View Related
Oct 2, 2008
I'm looking for a complete working example of how to write a single cell to a Access table using a SQL command. More to the point, i'm looking to UPDATE an access table field with a certain record ID with the current contents of a cell. I am using Excel 2003.
I would be most thankful you give you permission to marry my daughter.
View 4 Replies
View Related
Jul 20, 2012
How I can directly write info from an external application to an Excel sheet
Actually, I'm using a special dll collection to do it, but I would need to buy the 64 version soon. Problem is I use only one function of the entire collection so I want to look on how to create it my self. Basically, it took data from my charting software and write directly to my Excel Worksheet.Cell
View 3 Replies
View Related
Dec 11, 2012
I've created a User Form to input data on a dashboard where you can select a date from a dynamic list of dates. The list of dates is calculated using:
Code:
=(TODAY())-WEEKDAY((TODAY()),1)+2
The other dates are based on this date -7.
The User Form code:
Code:
Private Sub CommandButton1_Click()
Dim emptyRow As Long
'Make Sheet30 Active
[Code]....
This works perfectly for any date except dates from December. 3/12/12 (3rd Dec) turns to 12/3/12 (12th Mar) when it's copied to the cell. The same happens for 10/12/12 - becomes 12th October instead of 10th December.
View 6 Replies
View Related
Sep 23, 2013
Excel 2003
On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.
View 5 Replies
View Related
Aug 11, 2009
I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?
View 14 Replies
View Related
Aug 2, 2014
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies
View Related
Aug 12, 2014
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
View 5 Replies
View Related
Mar 12, 2014
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies
View Related
Mar 22, 2014
I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
View 10 Replies
View Related
Feb 27, 2013
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
View 2 Replies
View Related
Nov 17, 2008
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
View 9 Replies
View Related
May 14, 2014
Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.
How it would work: The person pulling out the inventory would fill out this form and click "Submit"
submitbutton.jpg
I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that.
View 14 Replies
View Related
May 8, 2014
Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;
=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))
View 5 Replies
View Related
Jul 28, 2014
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
View 2 Replies
View Related
Jan 7, 2013
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies
View Related
Apr 3, 2014
in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies
View Related
Feb 4, 2009
using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..
View 9 Replies
View Related
