There is a peculiar issue with Excel. I copied a value from SQL Server
3944410059732201
3949795741526301
The datatype is Varchar(255)
on to excel and I am seeing the following values on Excel
3944410059732200
3949795741526300
Any idea why is this happening.
Hi All,
I have Excel 2007. I have had a pivot table running for years. The pivot table is a simple timesheet. There are 3 Row fields, which are the project categories and subcategories. Vertical fields are the dates worked. Data area is a single calculated field showing the time (in decimal rather than hh:mm:ss) spent on each project on each date. The formula for this calculated field is =(DAY(Total)*24)+HOUR(Total)+(MINUTE(Total)/60) where Total is one of the columns in the pivot source data.
When our company switched from 2003 to 2007 my pivot table worked okay for a while. Then one day the pivot table began replacing the value "ADMIN" in the third row field (i.e. a project subcategory) with the number 0.75 in the row field area. I couldn't figure out how to fix it so reverted to an old version and copied across all the raw data. But, yesterday the error appeared again. Any suggestions how to fix it without copying across all the data from an old version?
If I change the word ADMIN in any way (e.g. add a space on the end) the 0.75 disappears and the modified word appears as I would expect. The pivottable does not like the word ADMIN for some reason and I can't figure out why.
Checks performed:
I don't have any named ranges called ADMIN.
The source data appears to be clean.
Can anyone help?
Thanks
Andrew
P.S. Unfortunately I can't figure out how to post a screenshot. The post needs a url, but I don't know what to do with that.
Hi all,
I've just posted a question and heres part 2 lol,
When i change a txtbox in my userform and somebody presses space i want to replace the space with a "_" instead (i'm using the name as a named range Title)
Any ideas?
Here's the code:
Code:
Application.OnKey "{ }", "_"
Cheers for looking folks!!
My macro runs when my file is opened.
The first thing I do is copy the value of one cell and paste it into another like this: Range("$E$6") = Range("$E$10")
Cell E10, the source cell, links to another file. What I want to do is get the value of that cell before it links to the other file so I can compare the difference. But, what happens is E6 copies E10 AFTER the linked values are copied - so both E6 and E10 are the same value, the value from the other file.
What can I do (without any manual intervention or reading a second file into my macro) that will give me the results I need?
I have a macro set up that replaces "RCVD " with "" in front of a date.
the problem with it is when it replaces it with nothing on specific dates it changes the day and month round into an american format.
eg
1. cell contains "RCVD 05/10/2009"
2. macro removes "RCVD " to leave the date (in serial format) so I can run calculations on it at a later date.
3. what is left is 10/05/2009.(it gets switched round!!!)
for some reason I cannot get it to work right.
If I manually do it it is ok.
FYI I am using
Selection.Replace What:="RCVD ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Any ideas would be great
Thanks......
In all my years, I have never experienced this before.
On one column in just one sheet of a particular workbook, I am losing whatever formukla is typed into the cell. The formula calculates the result and then displays the result but the formula has been completely replaced by the value.
Even in the formula bar, the formula is gone and only the calculated value is visible.
When I try to 'Evaluate Formula' it says the cell does not contain a formula but contains a constant.
I've tried deleting the column and starting again, but the same thing just continues to occur.
I thought it may be due to some VBA in behind the sheet, but that sheet doesn't carry any VBA.
I've looked through all the VBA I have in other sheets and Modules, but can find nothing that might cause this.
Please find, below, all the VBA code I have used for other sheets or modules ...
Private Sub Workbook_Open()
Sheets("Welcome").Activate
ClassSelection.Show
End Sub
Sub test()
Dim r As Range
For Each r In Range("m2", Range("m" & Rows.Count).End(xlUp))
If r.Value "" Then
Application.DisplayAlerts = False
On Error Resume Next
Sheets(r.Value).Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets("DataTemplate").Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = r.Value
End If
Next r
End Sub
Private Sub OptionButton1_Click()
Range("LookupTables!L1").Value = TextBox1.Text
End Sub
Private Sub OptionButton2_Click()
Range("LookupTables!L1").Value = TextBox2.Text
End Sub
Private Sub OptionButton3_Click()
Range("LookupTables!L1").Value = TextBox3.Text
End Sub
Private Sub OptionButton4_Click()
Range("LookupTables!L1").Value = TextBox4.Text
End Sub
Private Sub OptionButton5_Click()
Range("LookupTables!L1").Value = TextBox5.Text
End Sub
Private Sub OptionButton6_Click()
Range("LookupTables!L1").Value = TextBox6.Text
End Sub
Private Sub OptionButton7_Click()
Range("LookupTables!L1").Value = TextBox7.Text
End Sub
Private Sub OptionButton8_Click()
Range("LookupTables!L1").Value = TextBox8.Text
End Sub
Private Sub UserForm_Initialize()
With Sheets("LookupTables")
TextBox1.Text = .Range("LookupTables!A2").Value
TextBox2.Text = .Range("LookupTables!A3").Value
TextBox3.Text = .Range("LookupTables!A4").Value
TextBox4.Text = .Range("LookupTables!A5").Value
TextBox5.Text = .Range("LookupTables!A6").Value
TextBox6.Text = .Range("LookupTables!A7").Value
TextBox7.Text = .Range("LookupTables!A8").Value
TextBox8.Text = .Range("LookupTables!A9").Value
End With
End Sub
Private Sub CheckBox1_Click()
Dim x As Range, wsStudent As String
wsStudent = StudentA.Text
If CheckBox1 = False Then Exit Sub
Set x = Sheets(wsStudent).Columns(3).Find("*", SearchDirection:=xlPrevious)
If x Is Nothing Then Set x = Sheets(wsStudent).Cells(1, 3)
x.Offset(1, 0) = TodaysDateAndPreviousFiveDays.Text
End Sub
Private Sub TodaysDateAndPreviousFiveDays_Change()
TodaysDateAndPreviousFiveDays.Value = Format(TodaysDateAndPreviousFiveDays.Value, "[$-009] mmmm-dd")
End Sub
I'm posting a new thread instead of continuing from my other post 'Macro that extends cells to the last row' so as not to create confusion since I'm still hoping that someone will reply.
Anyways I have a macro that finds the number on column C and replaces that number with text. Example: 1000 represents Equity, 2000 represents Bonds, 3000 represents Swaps.
Everything works fine. The number will be converted to text, just the way I expect it to be BUT the conversion will only take place if the cell is active. Is there anything I missed out that is not enabling the conversion to run automatically? Here's the codes:
Code:
Public Sub Priority()
Dim s As String
Dim cell As Range
For Each cell In Selection
s = cell.Text
Select Case s
Case "1000"
cell.Value = "EQUITY"
Case "2000"
cell.Value = "BONDS"
Case "3000"
cell.Value = "SWAPS"
End Select
Next cell
End Sub
Thanks in advance.=)
I have a column of data that I want to extract the information to the left of the word "replaces". The data to the left side is different length. So using the function =LEN doesn't help me here. But all rows have the replaces as common in them. So I was hoping for a formula that would work off of that. Thanks again!!!
006-02-T replaces 006-01-T
01-100-0170 replaces 01-100-0390
01-103-0110 replaces 01-103-0040
01-126-011 replaces 95-0043
01-1-0060 replaces 01-150-0150
01-168-2014 replaces 01-168-1814
01-261-0340-01 replaces 01-261-0340, 95-0885, 95-0885-01
01-262-0550-999 replaces 02-262-0550
I just found this website and it's pretty awesome...I searched but couldn't find the answer..so I am looking for some help (or for someone to point me to a post) with an excel challenge.
Basically, I need to fix numerous formulas in 100 plus workbooks. The problems in every workbook are exactly the same, and (simplified) are as follows:
Problem 1:
Cell A3 in Sheet10 currently has the formula "=A7+A8" and it should read "=B9+B13"
Problem 2:
Cell C3 in Sheet25 currently has the formula "=B7+X8" and it should read "=Y7+D19"
Problems 3-10: Similar to 1 and 2 above.
I have about 10 formula errors out of 1700 formulas in each workbook. Instead of opening 100 workbooks and fixing the formulas manually (or even with find and replace), I was hoping someone could get me started with a macro using the example above. Then I can customize and repeat it to create one macro that fixes the problems and run it on all the workbooks. Thanks in advance for any help!
Hi, I'm trying to paste text from Excel to Word. I cannot get it to work
right - everything is pasted on line 1 in Word. The previously pasted text
gets replaces by the later pasted text. Can anyone help me out here?
Here's the macro I've been trying to use:
Sub TW()
Dim AppWD As Word.Application
Dim DocWD As Word.Document
Dim RangeWD As Word.Range
Set AppWD = CreateObject("Word.Application.11")
AppWD.Visible = True
Set DocWD = AppWD.Documents.Add
With DocWD
Set RangeWD = .Range
Sheets("T").Select
Range("A15").Select
Selection.Copy
With RangeWD
..Font.Name = "Arial"
..Font.Size = 12
..Font.Bold = True
..PasteSpecial DataType:=wdPasteText, Placement:=wdInLine
..InsertParagraphAfter
..Collapse wdCollapseEnd
..InsertParagraphAfter
End With
Set RangeWD = .Range
Sheets("T").Select
Range("A17").Select
Selection.Copy
With RangeWD
..Font.Name = "Arial"
..Font.Size = 12
..Font.Bold = True
..PasteSpecial DataType:=wdPasteText, Placement:=wdInLine
..InsertParagraphAfter
'.InsertParagraphAfter
..Collapse wdCollapseEnd
..InsertParagraphAfter
End With
End With
Sheets("S").Activate
End Sub
