Excel Name Trick #12 Part 3: Paste Name List

I am attaching txt file and excel file for your referance.
I am using VB macro (please look into Module1 of excel file) to Parse data from txt file and transfer into excel sheet.

Its working great, however i would like to add the date and Invoice # which is part of the txt
file to be pasted in column I and J as i have shown it manually in excel sheet cell I2,J2,...I3,J3...

eg : 2010-05-25-Asheer-CDI#455665 is the name of the txt file...
from here i would like to extract date (2010-05-25) and put it in column I
extract Invoice # (455665) and put it in column J

Also as you can see in VB code :

Set oFS = fso.OpenTextFile("C:/2010-05-25-Asheer-CDI#455665.txt", ForReading, False)


I would like to see if it is possible to manually enter the file name for reading by asking :

Please enter the location of the file to be read...and browse to the file..


Please let me know if you have any question.

Thank you...

Riz..

In excel file there are two sheets

1. Enty Sheet
2. Master Sheet

In entry there are following columns

Code | Sub Code | qty.

In Master Sheet there are following columns

Code | Sub Code


Master Data is as under

Code | Sub Code
101 | A01
101 | A02
102 | C022
102 | C021
103 | D02
103 | D03
103 | D04
104 | A02
104 | A01

In entry sheet when i ener Code in Column A , a list box / drop down showing all related values of CodeFrom Master Sheet to be shown in Column B of Entry Sheet.

Can you help me in this.

Thanks in advance.

Regards

Suhas

Hello, all,

I found this code below that copies a cell in Excel to a field (bookmark) in Word, but it loses all formatting on the Word side.

The issue I'm having is that text copied from Excel looks like

"36.84%"

but when pasted into Word via a macro or formula, it looks like this...

" 0.368421052631579 ".

Code:

 
Sub ExcelCells_to_Word
 
Dim WdApp As Object, wd As Object, ac As Long, ws As Worksheet
Set ws = Worksheets("Totals")
On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number  0 Then
Set WdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = WdApp.Documents.Open("c:\Apple, XX2.doc")
WdApp.Visible = True
With wd
 
       .FormFields("FIELD01").Result = ws.Range("B01").Value
 
 
End With
Set wd = Nothing
Set WdApp = Nothing
 
End Sub

Is there a way to modify the code above so that the number AND format are BOTH copied over to Word?

In other words, keep the formatting of the numbers?

Note that I had a very similar problem on another post here that Smitty helped me out on (thanks again Smitty!)

http://www.mrexcel.com/forum/showthread.php?t=421795

This was to keep the formatting of currency and he suggested this..

Code:

.FormFields("FIELD01").Result = Format(ws.Range("B01").Value,"$##,###.00")

Which worked perfectly, I tried manipulating the part after ".value" but had very strange results. Can anyone figure this one out (including you Smitty, if you're there )

Thanks much!

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Looking for a little help tweaking Excel Magic Trick 185. I've reached what is probably just a mental block.

I'm trying to adjust a spreadsheet for work using the processes described in Excel MagicTrick 185. (Great series, by the way...) The catch is that the source data table can not be modified under any circumstances (we've already asked).

The problem is different than EMT_185 in that there are 9 columns that can potentially define a match as opposed to just the one shown in EMT_185. There are two user options ('I-III' and 'A-C') which in combination establish the headings for the 9 possible conditions (I-A, II-A, III-A ...) .

(See the linked spreadsheet) I've been able to:
determine if a row in the source matches user provided conditions (col Y),
get the count of matched source rows (row 26),
determine the match instance (col AA), and
place the source data in the output table (col AC)

What I can't quite get my mind around is suppressing the blanks in the output (i.e.:getting match 1 on row 1, match 2 on row 2, etc.)

What am I missing? Please help. A spreadsheet showing what I have so far is located on google docs. Perhaps you could make a new EMT out of it!

BTW.. this used to be done via vba, however that is no longer an option (don't ask... It just isn't, and it's not a topic that is open to discussion -- we tried).