YTLE#66: VLOOKUP replaces IF, Again!

Video | Similar Helpful Excel Resources

See how to use the VLOOKUP function in a formula to replace a huge, long IF function formula.

Got a Question? Ask it Here in the Forum.

Similar Helpful Excel Resources
There is a peculiar issue with Excel. I copied a value from SQL Server
The datatype is Varchar(255)

on to excel and I am seeing the following values on Excel

Any idea why is this happening.

I would like to check in column E if there is an cell with external
If there is a cell with External a popup needs te be shown and ask replace by 258698307
Column E can vary in length
Hope someone can help me!

Much appreciated!
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?

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?

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:


Application.OnKey "{ }", "_"

Cheers for looking folks!!

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 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.
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, _

Any ideas would be great

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()
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
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:


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 macro that copies and pastes some data from excel into a word document that has been previously created and saved. The problem is that the macro will replace everything in the doc (2 pages worth of) with the copy and paste selection. How can this code be modified to add the selection to the end of the document rather than replacing everything in the document?

Please Login or Register  to view this content.