Read The Cell Offset 3 To The Right, And Then Set To Either YYYYMMDD Or YYYY Depenig On The Value In The Cell

Jan 29, 2010

ForEachCInSheets("data").Range("A2",Range("A2").End(xlDown))
WithC
IfIsDate(.Value)Then
.Offset(0,3).Value=Format(.Value,"yyyymmdd")
Else
.Offset(0,3).Value=Right(.text,4)
EndIf
EndWith
NextC

which is supposed to read the cell offset 3 to the right, and then set to either YYYYMMDD or YYYY depenig on the value in the cell. I'm not sure how to specify the first part of this line - .Offset(0, 3).Value = Format(.Value, "yyyymmdd") - it keeps reading the cell in column A, rather than the offset cel.

View 3 Replies


ADVERTISEMENT

Convert YYYYMMDD To DD/MM/YYYY

Aug 24, 2007

My spreadsheet imports a date field and displays it as YYYYMMDD.

In A2, I use the formula =Right(a1,2) & "/" & Mid(a1,5,2) & "/" & Left(a1,4) and this converts it to DD/MM/YYYY

However, I can't find out how to write this into my VB Macro.

I've tried:

Range("a1").FormulaR1C1 = "=RIGHT(RC[-1],2) & ""/"" & MID (RC[-1],5,1) & ""/"" & LEFT (RC[-1],4)"

View 8 Replies View Related

Cell Date Formatting: Cell To Display Mm/dd/yyyy And Enter Mm/dd/yy

Aug 13, 2008

I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.

View 2 Replies View Related

Using OFFSET Function To Return The Value From Another Cell With Row And Column Offset

May 31, 2014

I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.

For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.

I have attached a sample worksheet that provides some examples.

View 3 Replies View Related

Count Hours In DD/MM/YYYY HH:MM Cell?

Apr 3, 2014

I have to count the number of hours within a cell which has also dates. Example below.

12/03/2014 04:00
12/03/2014 04:00
13/03/2014 07:00

What is the formula to count the number of hours given the date ranges need to be in the same cell and the dates and hours are likely to change ?

View 6 Replies View Related

Typing 230713 Into Cell Comes Out As 01/09/2531 Instead Of 23/07/13 (dd/mm/yyyy)

Feb 24, 2014

when I type a 6 digit date into Excel cells formatted to dd/mm/yyyy very strange dates are being displayed. Tried opening a new book - just the same result. All is ok typing with the / in between but I don't usually have to do that. Regional settings have been checked and formatting etc.

View 3 Replies View Related

Macro To Convert Dates Format From Yyyy Mm Dd To Yyyy-mm-dd

Feb 25, 2009

I have a 20,000 line Excel sheet...

And all the dates are entered like this:
2009 02 08

I want to add a dash, so they look like this
2009-02-08

so I added a command button, and this code, but its not searching the sheet and adding the dash's...

View 14 Replies View Related

Read Workbook, Worksheet & Cell Address From Cell

Sep 21, 2007

Display Alert On Closing If Cell Not Filled In

How do I alter this code so that it acts like an INDIRECT function so will pick up a cell reference in another cell?

I can't just use A1 as I want this to change dynamically.

View 4 Replies View Related

VBA Incorrectly Changing Some Dates From DD/MM/YYYY To YYYY-DD-MM

May 1, 2014

I have the macro below which opens csv files stored in a user selected folder and processes them changing the date format in column D from DD/MM/YYYY to text stored as YYYY-MM-DD.

For most of the dates the code works without issue, but for some (those with a month <12 possibly) it transposes the MM and DD incorrectly.

I understand that when opening the CSV's in excel it automatically converts the dates to DD/MM/YYYY, so I'm actually opening in wordpad which displays as YYYY-MM-DD, with only a portion being incorrect.

I've attached a couple of sample files (pre and post conversion).

Status_20post.csv‎
Status_20pre.csv‎

View 9 Replies View Related

Userform Changes Date From Dd/mm/yyyy To Mm/dd/yyyy On Spreadsheet

Mar 20, 2014

creating a user form that will make my job of recording data far easier. However, when the date is entered, it changes it form UK to US.

I've checked the cells and they are formatted for UK dd/mm/yyyy.

The script is as follows:

Private Sub UserForm1_Click()
End Sub
Private Sub DataInput.UserForm_Initialize()

[Code].....

View 2 Replies View Related

Cell Referenceing (display The Value In The Cell Offset By X So I Can Bring The Numbers From Sheet1 Over)

Jul 17, 2009

Sheet1 is a list of names, then a few columns of numbers.
Sheet2 contains calculations based of the numbers in sheet1.

Each sheet from 2 on is names after the names in sheet1 column A...
A large cell at the top of each sheet contains the formula to display the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

So what I want to do now is...
in cell, look for the cell in sheet1, and display the value in the cell offset by X so i can bring the numbers from sheet1 over.

In plain english the cell would say like...
"look in sheet1 for "Name" then display the cell next to it"

I know to look in the next sheet its "=Base!B2" if i wanted b2 on sheet base.
I know to use "=OFFSET(Base!B2, , 1, , )" too look at B2 and go right 1 column.
But the issue is i dont want Base!B2.. i want whatever cell in Base column A that has the text that A1 has.

View 2 Replies View Related

Find Last Cell In Range, And Changing Another Cell Based On Offset Text

May 4, 2009

If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"

and also t = time()

View 7 Replies View Related

Find All Instances Of Cell In Column & Check Offset Cell

May 21, 2008

how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!

the macro will look at the value I entered in cell A1, then find all instances of it in Column A
in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)

View 7 Replies View Related

VBA - Find Cell Containing Text And Use An Offset Cell Value In Formula

Feb 13, 2014

I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.

VB:

Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"

I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.

I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.

View 1 Replies View Related

Formula With Offset From Active Cell And Sum Until Blank Cell

Dec 24, 2013

I have a formula in cel B1 : =SUM(A2:A100) / A1

I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.

So I would need something like (literally):

=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)

View 3 Replies View Related

Check Each Cell In Range For Value & Put Text In Offset Cell

Jun 7, 2008

I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.

Sub RatingsFix1SP()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
For i = 0 To 3
For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 6) = 0.15
End If
Next rngCell
Next i
End Sub

To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.

View 5 Replies View Related

Convert Date From DD-MON-YYYY Into YYYY-MON-DD

Mar 20, 2014

I want to convert to date from DD-MON-YYYY into YYYY-mm-DD format in column J.

It is also applicable to date separated by semicolon. It was working previously but not now.

HTML Code: 

Sub DATEE()
Dim rng As Range
Dim rngTotal As Range
Dim i As Integer
Set rngTotal = Range("j1:j" & Range("j" & Rows.Count).End(xlUp).Row)

[code]....

I made one mistake . date conversion should be 2014/03/11 if orignial date is 11-MAR-2014

View 4 Replies View Related

Date Formatting (changes From Dd/mm/yyyy To Mm/dd/yyyy)

Mar 3, 2009

I finally finished programming the userform that I've been working on for about a week and started to populate the database with info (dates, names, TRUE/FALSE etc etc).

Then all of a sudden I notice that as I switch between "records", one of the dates gets a life of its own - it changes from dd/mm/yyyy to mm/dd/yyyy as I select it. When I jump onto another record and select that previous record again, the format changes from mm/dd/yyyy to dd/mm/yyyy.

This happens with ALL dates where the days and months are =< 12.

I thought I'd apply formatting to the text box to stop that happening with the code below:

View 3 Replies View Related

Read Cell Value To Point To Another Cell?

Mar 11, 2013

i want a formula reading the value of a cell and then using it to insert the value of another cell. example:

125 =s125
289 =s289

View 5 Replies View Related

Read Only The Number Value Of A Cell

Jun 12, 2009

I am trying to get a SUM function to recognise values within a range of cells, that are immediately followed by a *. i.e. 34*

For each range i need to count the total number of inputs, count the number of starred inputs & add up all the values entered.

Currently COUNT doesn't recognise starred values, COUNTIF is set to count only starred values, and SUM wont recognise the value of the starred values.

Is there a way to only read the number element of an input - there are only integer values & starred integer values. The starred inputs are also conditionally formatted. If there is a way would it then affect the COUNT/COUNTIF element?

Pedro

View 11 Replies View Related

How To Make Cell Read Only

May 9, 2006

I have tried the following code, but doesn't work unless I protect the sheet:

Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False

If I protect the sheet, then it works fine, but how can I make a cell read only

WITHOUT protecting the sheet?

View 13 Replies View Related

How To Read The Name Of The Active Cell

Dec 4, 2006

When I am clicked inside a cell how can i with VBA get the defined named?

View 9 Replies View Related

IF And OFFSET: How To OFFSET Only If Cell Has A Value In It

Oct 26, 2008

I have attached the .xls file.

The basic setup is as follows:

(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)

(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.

For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc

As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.

(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.

NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.

OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.

I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.

HOWEVER, what i WANT is the output as shown in columns Y-AF.

So as you will notice, there are 2 problems:

(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.

How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?

(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.

Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.

View 7 Replies View Related

Format Date From (yyyymmdd) To (mm/dd/yy)

Aug 30, 2007

I have a column where the date is provided under this format => 20070825 (yyyymmdd)
I would like to convert 20070825 to display into this date format => 8/25/07.

View 9 Replies View Related

Use VBA To Read Cell Value And Post In Another Sheet?

Feb 11, 2013

I am trying to read a value in a cell to post in another sheet.

Once the value has been copied, end. However, if the cell to the right of the copied has a value, then that value should be copied instead.

If the cell is blank, then the information should not be copied.

Basically I have a string of data from left to right being populated, with the most current value being copied to a differnt sheet.

View 7 Replies View Related

How To Make A Cell Read Only Decimal

Jul 1, 2013

I'm trying to create a formula in a cell that references another cell but I only need it to reference the decimal. How would I go about doing this and is it even possible?

View 3 Replies View Related

Read Data From Web By Using Link From Cell?

Aug 1, 2009

Iit is possible to do somethiling like this? I wanna load some data from web but I need link read from cell (e.g. sheet data!B1) .. so it means :

View 3 Replies View Related

A Loop To Read One Cell And Print To Another

Jan 25, 2010

I am trying to program a loop in Excel VBA to read the first two characters of a cell in column A and, depending on the characters returned, to print something else in column B, then move to the next row up. What I have so far below is pieced together from little insights from over the web, but currently it does nothing. Not even error messages. It has more fields (if "XX" > print blah blah) but they are all the same, so only the first two iterations are shown here for clarity.

View 8 Replies View Related

IF Statement To Read Text In A Cell

Dec 29, 2008

I need a statement to look at how many words are in a cell. If there is 4, to return "Stem" to another column, if it's the 5th word or higher then to return the last word in the string.

For Example

Column A.......................................................... .Column B
SOME-TEXT-GOES-HERE....................................../...STEM
SOME-TEXT-GOES-HERE-SOMEMORETEXT.............../...SOMEMORETEXT
SOME-TEXT-GOES-HERE-SOMEMORETEXT-AGAIN...../...AGAIN

I have tried but failed miserably! I though of using a lookup statement but due to the number of variations it would be quite time consuming.

View 8 Replies View Related

Macro To Read Cell Comment

Apr 5, 2013

I want to design a macro to check if the cell has a comment in it. If so, the macro should read the comment and store it in a variable.

View 2 Replies View Related







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