Posts by Samurai

I'm not sure if anyone can help me as this is not Excel VBA.

I need to use an IF statement to find text in a word document and if doen't find it, Then I will insert autotext.

I know how to find and I know how to insert autotext, but I don't know how to use these with IF in word VBA.

If anyone can help?

Dude, you've been a great help and I've been an idiot. You answered this question for me a couple of weeks ago. If I had done what you had suggested then......

Oh well.....

Thanks again.

Samurai

Hmm...That's the problem....the bloody toolbar.

If the toolbar is there already and I open the workbook with macros enabled, then everything is unfrozen.....

So, is there a way to "attach" a toolbar to a workbook so it is visible on opening and "remove" the toolbar when you are finished with the workbook?

sigh...I hate excel

Ahah.....I knew that

Ok. I tried disabling the macros, still freezes....

wierd.

Unfortunately, I need the macros enabled in this workbook.

I don't have any macro set for when the workbook opens, except that I have a customized toolbar attached to the workbook. The only command I have is for the toolbar to delete when I close the workbook.

That has to be the problem, but I don't see the fix.....

Maybe I didn't explain clearly.

I can maximize excel (the application), but I can't maximize, resize or click on sheet tabs for the workbook I open. After I maximize excel, then the workbook unfreezes.

When ever I open a particular workbook, it is frozen until I maximize my excel screen. The freeze stops me from clicking on Sheet tabs, Cells and even maximizing the workbook itself. Once I maximize excel everything works ok, but it is annoying as hell.

Any ideas would be appreciated/

Samurai

I think I need a brain!!

Thanks dude!!

Can anyone tell me why this doesn't Exit Sub when I click the Ok button on the meassage. Instead the macro keeps running and when it doesn't find the test.txt file it crashes.

MyFile = Dir("C:\test.txt")
If MyFile = "" Then
answer = MsgBox("Test file not found", Buttons:=vbOKOnly)
If answer = vbOKOnly Then
Exit Sub
Else
End If
End If

Elegant if enough if it works!! Thanks dude

Hey Guys,

I have another one that is stumping me.

I have a sheet which is being populated by a text file.
Column contains Product Categories, Column two contains Product Descriptions.
The sheet is already sorted, so I don't need to do that. What I need to do is be able to clear not delete all duplicate Product Category entries.

Eg:
Product Category Product Description
Bolts Big Bolts
Bolts Small Bolts
Bolts Medium Bolts
Nuts Round Nuts
Nuts Square Nuts
Nuts Small Nuts
Screws Etc
Screws Etc1
Screws Etc2

What I need is VBA that will go through Column A and Clear only the duplicates. The positions must remain the same and no more sorting needs to be done.

Thanks,

Samurai

Thankyou!!!!!!!!!!!!!!

Hey Guys,

Scratching my head on this one.

Sheet2 has a column of part numbers starting at A4 and a second sheet with a column of part numbers which are contained in the named range: partsmatch.

The formula below is in cell B4 in Sheet2 and the idea is that where a part is removed from the named range partsmatch, the message "Product not found" is displayed, and if the parts all match up, nothing is displayed.

The formula works if I delete anthing from the middle of the range, but if I delete the first part number I get #N/A

=IF(A4=VLOOKUP(A4,partsmatch,1),"","Product not found.")

Any ideas or alternatives to VLOOKUP?

Thanks,

Samurai

Hey guys,

I am importing data from text file, which works fine.

Can someone throw me a few lines of vba that will Exit Sub and pop-up a message box if the text file is not found?

Thanks

Samurai

I have the following code, which some of you helpled me out with.

Can someone help me modify this so that when I run the macro, it copies the first row (B22), which contains formulas and pastes it to the second row (B23), then the next time I run the macro, it copies the first row again and pastes it to the next row (B24), without changing the first or second row and so on until it reaches B41, where it can't go any further.

Thanks

Sheets("Quote").Select


' Take exisiting data and move down one row (again call it a range and it will continue to expand as you require it)
Range("B23:H40").Copy
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteAll


' Take what is to be copied and paste values only into the rows 2 below
Range("B22:H22").Copy
Range("B23").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders


' select A3 to place the cursor at the start point
Range("B22").Select
Application.CutCopyMode = False

Thanks for the reply dude.

I have a custom toolbar with all macros attached to particular workbook. When I open the workbook the toolbar appears as it should, but how do I set it to remove the toolbar when I close my workbook?

Thanks,

Samurai

Andy,

Thanks for the reply. I think I may have explained my problem incorrectly.

Let me start again:

The text file contains rows of product and price info only:

partA $30
partB $25
partD $40

This is imported into the sheet

first column is part numbers, second column is price and third is discount

partA $30 2%
partB $25 1%
partD $40 3%

I then get new data from the text file that adds a new part and also may have different price info. This will always be in alpabetical order, so some part numbers may be appended at the bottom, some may be inserted in the middle:

text file

partA $35
partB $25
partC $50
partD $40

sheet

partA $35 2%
partB $25 1%
partC $50 3%
partD $40

So now the percentage that was supposed to be assigned to partD is now assigned to partC.

I need to be able to update existing info in the sheet and insert new rows

Any ideas?

I have a tab delimited text file with rows of product and price information.
This file will get dumped from a database into the text file and I am importing this data into a sheet.

text file:
A 20
B 21
D 23

Sheet:
A 20 2%
B 21 1%
D 23 3%

If the text file gets updated and new products are added to the list it messes up my sheet, like this

Text file:
A 20
B 21
C 25
D 23

Sheet:
A 20 2%
B 21 1%
C 25 3%
D 23

How I can set my import up, so that it updates the sheet by updating any price info on existing products, but also inserts new product info so that my other columns aren't messed up.

This is all being done in VBA, by the way.

Thanks,

Samurai

Sorry for the delay in replying, I figured this one out.

The guy who built the template, was using Autotext to insert. I just needed to make changes to the autotext and save over it with the same name.