Posts by mooreydp

Re: Validation Of Data On Worksheet_change Event

Hi Warrior,

First and foremost mate. I'd remove the on error resume next statement so that when you do have an error you can isolate where it occurs.

Obviously your problem is if the vlookup returns an error you don't want it to debug. therefore try using the vba find function instead: (I'd suggest wrapping it into your own function for later use as this becomes extremely handy)

e.g.

Sub PassVLOOKUPtostring()


    Dim sResult As String
    
    If Range("CLI").Find("hello", , xlValues, xlWhole, xlByRows, xlNext) Is Nothing Then
        sResult = "error performing search for 'hello' in Range 'CLI'"
    Else
        sResult = Range("CLI").Find("hello", , xlValues, xlWhole, xlByRows, xlNext).Value
    End If
    
    MsgBox sResult
End Sub

Now as for point 1. the userform opens regardless due to your On Error Resume Next statement not controlling the error thrown by your vlookup formula. as mentioned above try the find function the way i have shown and you will get much better results.

With regards to point 2. You haven't declared textbox1 as Userform1.Textbox1 and again due to your error handling it is just running on through.

hope this helps.

My tip is always remove On Error resume Next statements. They cause way too much hassle and i guarantee that you can get by without them provided you learn a little bit more about what you are trying to return from various functions.

Re: VBcomponent: Remove Before Close

I had the same issue. It seems to surround the internal disposal method of Excel. Thus it needs to action the disposal event in its own time rather then complete the event immediately when the .Remove method is executed.

I wouldn't be surprised if this relates to the issues surrounding the .Net disposal methods either. You can trigger them as and when you wish but they don't necessarily remove the objects from memory immediately after you execute the disposal method.

I'm curious if anyone has this problem whether they have found my solution to work. As I mentioned earlier, I pinpointed the EnableEvents method because this seemed to resolve my problem. I would be very interested if anyone has any feed back as I'm interested to know why it works in some scenarios and not in others.

Re: VBcomponent: Remove Before Close

As per above this is the userform file (please change the extension from .txt to .frm)

Hope it helps

Moorey

Re: VBcomponent: Remove Before Close

Hi Tom,

As they say a picture paints a thousand words. I've attached an example of my workbook with the form removed. (I removed the form because the workbook was 100+ KB) I'll attach the userform file separately in the next post.

I have tested this on a project that I was having problems with and it worked seemlessly. I pinpointed the EnableEvents property because this was the final step i had to include before it would work, but I guess it could also be down to the order of steps.

Let me know how you get on and whether or not there are any further questions.

Regards

Moorey

Re: VBcomponent remove before exit

I realise this thread is quite old, but as i just encountered a similar problem I thought i'd identifiy my resolution.

I was trying to export and remove all components from an old workbook and replace them with components from an up-to-date workbook. Quite frequently i would remove a component but excel wouldn't release it.

I determined that it was Excel level events which were holding up the process and DoEvents had little to no effect. My resolution was to set the Application.EnableEvents property to False prior to any Export and Removal code.

For now this seems to have addressed my problem. The code in my project is quite extensive but if anyone is suffering the same problem with VbComponents.Remove then feel free to Reply and I will try posting it.

Kind Regards

Moorey

Re: Import Data - Source Path

Hi mate,

I'm of to bed shortly, but i'd say that there is some data to the right of your query or your query is pulling in more data then there is columns.

Try deleting some of the data to the right of your query or insert the imported data into a new worksheet

Hope this helps.

Moorey

PS If that doesn't help i'll check it in the morning. Cheers mate

Re: A Survey Of Spreadsheet Users

Hi guys,

I'm a little late coming into this discussion as i've been off the forums for some time but I thought this may interest you on the productivity side.

For the last 2 years i have been working for one of the major asset management banking programs in the UK. When i first arrived everything was done rather manually and tonnes of the work was extrememly repetitive. I found that some of the core systems were extremely limited in their ability to process "non standard" data and as a result i was brought on to develop flexible and tactical business solutions.

I have seen time frames reduced from several hours a day to mere minutes, data has been controlled with data interrogation import routines and data interrogation export routines. We have now linked Excel VBA apps into our core systems and have enforced strict control on version control and backup regimes etc. What i have seen in the last 2 years is that Excel USED PROPERLY can out compete more than 90% of the stand alone solutions in the market place, and what's better - nearly every business has it installed and ready to go.

As for the critics - the next guys that says "Excel isn't robust enough" and points me in the direction of a C# .Net application developed by an IT company with absolutely no business knowledge might want to reconsider what he calls robust. (That coming from a guy who's latest hobby is C# .NET : D )

Being able to teach users how to control and maintain an application can be risky but it can also be worth its weight in gold.

Moorey

PS. Carl i'd be happy to offer examples of costs saved, I think you would be amazed.

Kind Regards

Re: Conditional Locking Of Cells Based On Dropdown Value

Hi Kartick,

I'm just about to head off but this should get you out of trouble.

If you insert the following code into the code section of Sheet1 as it appears in your example workbook then it should get you out of trouble.

Please note that as it protects your sheet to ensure the locked cells are validated then you may wish to unlock all the cells in your validation list so you can change them back and forth as it suits.

Private Sub Worksheet_Change(ByVal Target As Range)


    'Bear in mind that cells being locked is only affected if you
    'have sheet protection in place i am going to assume that you have
    'a null password
        
        
    'Procedure level declarations
    Dim rngCell As Range
    Dim lRanges As Long
    
    'Loop through each cell in Target
    For Each rngCell In Target
        
        'Determine whether the target range is in column c
        If rngCell.Column = 3 And rngCell.Value = "Yes" Then
            
            'Unprotect the sheet
            Sheet1.Unprotect
            
            'Perform the vlookup in the adjacent cell (Column D)
            rngCell.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]=""Yes"",VLOOKUP(RC[-2],data!R2C1:R13C2,2,FALSE),"""")"
            
            'Lock the formulated cell in column D
            rngCell.Offset(0, 1).Locked = True
            
            'Reprotect the sheet
            Sheet1.Protect
            
        'Just in case you need to insert an elseif statement to unlock the cell
        'if the value is not equal to yes then the following ElseIf Statement
        'and its corresponding code should get you out of trouble
        ElseIf rngCell.Column = 3 And rngCell.Value <> "Yes" Then
            
            'Unprotect the sheet
            Sheet1.Unprotect
            
            'Unlock the formulated cell in column D
            rngCell.Offset(0, 1).Locked = False
            
            'Remove the formula as well
            rngCell.Offset(0, 1).FormulaR1C1 = ""
            
            'Reprotect the sheet
            Sheet1.Protect
            
        End If
        
    Next
    
End Sub

I hope this helps mate

Moorey

Re: Import Data - Source Path

Hi Luke,

As i don't know what your file is etc I can only hope that it is the error i picked up on in your destination code.

Sub addfile()
     
    '>>>> This is incorrect
    'With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\a\projects\" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell _
        ))
       
    'Try this instead
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\a\projects\" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell.Address))
        .Name = ActiveCell.Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 852
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
     
End Sub

Note that the address property is required because you are trying to parse a range object to an existing range object when it actually requires a string address.

Let me know if this isn't the case. But i tried an example of my own and that is where it fell over.

Hope this helps

Moorey

Re: Range In An If Then Statement

Hi Syntinal,

I'm not sure if this is exactly what you want but if you wish to check the value of each cell in the range then you probably need to do something like this:

Sub SearchForMatch()
    
    'Procedure level declarations
    Dim rngCell As Range
    Dim bMatch As Boolean
    
    'Loop through each cell in the nominated range
    For Each rngCell In Range("B4:B50")
    
        'If there us a matching value then set your
        'boolean flag to true and exit the loop
        If rngCell.Value = TextBox1.Value Then
            bMatch = True
            Exit For
        End If
    
    Next
    
    'If a match was found then alert the user
    If bMatch Then MsgBox ("Name already exsists.")
    
End Sub

I hope this helps

Moorey

Re: Run Time Error 9: Subscribt out of range

gday helmekki,

check that the workbook you are searching in is the active workbook. And also ensure that the sheet name is actually "sheet3".

Subscript out of range usually means that you can't gain focus to the object you are searching. Hence it is out of range (you can't get hold of it)

Most people would prefer to use the code name of the sheet they are searching so that it isn't potentially changed by the user so in this case you might want to check the code name and refer to it like Sheet3.Range("A3:A3000").Find etc. NB. The code name can be changed by selecting the properties of each sheet in the VBE.

Hope this helps

Re: Code to import textfiles

Hey Rob,

If you replace the following code segment

sFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select the file to import...")

with

sFileName = "result" & iLoop + 44 & "kenmerk10.txt"

Then you will get the following file names in your consecutive loops.

result45kenmerk10.txt; result46kenmerk10.txt; result47kenmerk10.txt
result48kenmerk10.txt; result49kenmerk10.txt

Additionally you might need to enter a file path in front of the text file name e.g.

"C:\Temp\result" & iLoop + 44 & "kenmerk10.txt"

Hope this is more helpful

Re: Code to import textfiles

Hey mate,

This isn't perfect because i don't know your file names and i'm having one hell of a monday, but hopefully this can help you get unstuck.

Let me know if it makes sense, and if u want u can send me a private message and we'll sort it out.

'Make sure you add a reference to the Microsoft Scripting Runtime
'Usually under C:\WINDOWS\System32\Scrrun.dll
'Go to Tools, References, Browse in your VBE menu


Sub SampleTextStreamRead()


    'Declare procedure level variables
    Dim objFso          As FileSystemObject
    Dim objFile         As File
    Dim objTextStream   As TextStream
    Dim lRow            As Long
    Dim iColumn         As Integer
    Dim iLoop           As Integer
    Dim sFileName       As String
    Dim vLine           As Variant
    
    'Loop through each of the relevant import files
    For iLoop = 1 To 5
    
        'I'm not sure how to incorporate your variable file names as i don't know what they
        'are - in the meantime this will give you the opportunity to select the file you want
        sFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Please select the file to import...")
        
        If sFileName = "False" Then
            'The user has pressed cancel or Esc
            Exit Sub
        End If
    
        'Create an instance fo the FileSystemObject
        Set objFso = New FileSystemObject
            
        'Get a handle on the file
        Set objFile = objFso.GetFile(sFileName)
        
        'Open file for Reading
        Set objTextStream = objFile.OpenAsTextStream(ForReading, TristateFalse)
    
        'Loop through each line in the file and record the data to the activesheet
        Do While Not objTextStream.AtEndOfLine
        
            'Define the active row number
            lRow = lRow + 1
            
            'Split the line into an array of column values delimited by ;
            vLine = Split(objTextStream.ReadLine, ";")
            
            'Loop through each field in the array and record in the active row
            'in order or the different columns
            For iColumn = LBound(vLine) To UBound(vLine)
                Cells(lRow, iColumn + 1).Value = vLine(iColumn)
            Next
                
        Loop
        
        'Close the text stream object
        objTextStream.Close
        
        'Remove object variables from memory
        Set objFso = Nothing
        Set objTextStream = Nothing
        Set objFile = Nothing
        
    Next


End Sub

Hope this helps

Re: Demographics of Our Members

Happy Birthday mate.

Moorey

Re: Code to import textfiles

Hi Rob,

Can you answer a few extra details such as:

1. Will the five different filenames remain the same?
2. Do you want the details appended on top of each other?
3. Is there predefined structures for the text files e.g. 5 columns each with text etc?
4. Do you have to use the external data method or would you be just as happy to have a text stream reader to read directly from the text files and write it dynamically to the sheet?

The positive is that there are a lot of different ways you can accomplish this task, we just need a few extra details.

I'll try my best to explain all steps.

Regards

Moorey

Re: Copy module to another workbook

hey mate,

The best source of information (in my opinion) for programming to the VBE is chip pearson.

try:

http://www.cpearson.com/excel/vbe.htm

If it doesn't work then type programming to the vbe in a google search and you should be sweet.

Hope this helps

Re: vba macro (should be easy)

It's a first for me with my three finger pecking habits : D

But if there is one thing better than good advice it has to be the same good advice from two different sources

Re: Temporary disabling of workbook event

Hey mate try

Application.EnableEvents = False to turn events off and True to turn them on.

Hope this helps

Re: vba macro (should be easy)

hi sartorius,

you could probably use something like this:

Sub ConvertNonZerosToOnes()


    'Procedure level declarations
    Dim rngCell As Range
    
    'Determine whether the selection is a range
    If TypeName(selection) <> "Range" Then Exit Sub
    
    'Loop through all cells in the selection and change their values accordingly
    For Each rngCell In selection
    
        If rngCell.Value <> 0 Then rngCell.Value = 1
        
    Next


End Sub

Please note that you can't undo macros, so it may not be the best solution to overwrite values unless you absolutely have to. an alternative would be to use an If function to determine whether the cell concerned is 0 or not.

Regards

Re: hiding an error with if(iserr(...))

Hey mate,

You're not actually capturing the error in its first instance. You need to capture the initial instance of C10/B10 with another IsErr statement.

The error you are getting is a result of C10/B10>=1 not Iserr(C10/B10)

therefore you should write

=IF(IsErr(C10/B10),'Error',IF(C10/B10>=1,20,0))

Hope this helps