Posts by lidster

Hi guys,

Trying to get my code to loop for one minute.

Have tried this code (after searching through a few threads):

Sub LoopUsingTime()


x = 1


EndTime = Now + TimeValue("00:01:00")
For lCount = Now() To EndTime


      Sheet1.Cells(x, 1) = "the time is now " & Now()


      Sleep 5000


      x = x + 1
     
Next lCount


MsgBox ("loop finished")


End Sub

It will loop once only before finishing. Does anyone have any ideas?
Does a loop have to use integers only?

many thanks

Re: Wait Until Script Is Finished

cheers mate,

Finally got it to work.

Needed to add "wscript" in to the line

hProg = Shell(PathName, WindowState)

I assume this is because Im using office 97?

thanks a lot

Hi,

I am running a VBS file from excel when the spreadsheet is first opened using this command:

Shell "wscript c:\stuff\test.vbs", vbNormalFocus

this works great, but could somebody advise me how to wait until the VBS script has finished executing before calling my next sub in excel?

The test.vbs file can take anywhere from 10 secs to 1 minute to execute. I have considered putting a wait command in for 1 minute but would rather not do this.

thanks

Re: Find Locate Cell and Copy 1 Row Below

This is the new code that Im trying

Sub Dogs()
    Dim rd As Worksheet, x As Date, i As Long
    Dim fRng    As Range, crit
    Dim z As String
    
     
     Application.ScreenUpdating = False
     
    Set rd = Sheets("Pets")
    i = rd.Cells(Rows.Count, 2).End(xlUp).Row
    Set fRng = rd.Range("B16:B" & i - 1)
     
    crit = "DOG"




    z = "DOG"
       
    With fRng
        If Application.WorksheetFunction.Count(crit) > 0 Then
            .Offset(1, -1).Value = z
           
          
        Else
            MsgBox "String '" & crit & "' Not Found!", vbInformation + vbOKOnly
        End If
    End With
    
    Application.ScreenUpdating = True
    
End Sub

I think I am having problems defining the range properly.

What I want to do is find every instance of DOG in column B, and then type DOG into the cell left 1 and down 1.
I think I am there or there abouts now, but im almost certain that I am not defining the range correctly.....

can anyone help please?

Re: Find/Locate Cell & Copy 1 Row Below

ShosMeister

Thanks a lot, Im almost there,
The code works brilliantly for the sample that I sent to you, however sometimes it doesnt look so well filtered every 2 rows. I may have situations where there could be a lot of garbled info in between rows.(the sample file was cut down so it would fit on this forum and showed an ideal filter sample).....
I definately want to add a new column before column A and copy all instances of "DOG" and the line below to a new sheet.
I assume I will need to use a Find and countif method..... sorry Im still learning, I know what method to use and why- but I can never get it to work out properly....
I need to be able to find all instances of DOG and then copy that row and the row below (it is always the row below) to form 1 row on a new sheet.

any ideas from anybody would be much appreciated

thanks

Re: Find a cell & copy the row below to new location

Quote from ShosMeister

How is the data imported? Rather than go through this issue, it may be easier/better to handle moving the data in the row below DOG to the same row as DOG (which it appears it should be anyway).

ShosMeister,
The data in the attached sheet, has already been through several filters and vba code, to get it looking as it does from the original text files. I really dont want to have to amend the raw data format unless absolutely necessary.
If you know of a way of moving every instance of "DOG" to the line below, then I would be very grateful. I would also be able to eventually incorporate it into the ealier code and filters.

Would the "DOG" merge into the col A cell below something like 'move before (found string) & (A:found row -1) ...

this would be absolutely perfect if it did....

thanks for all your assistance

Hi Guys,

I have a spreadsheet formed from a text file.
I want to search for each instance of "DOG" in column A, and copy the row below it and then paste that entire row to a new sheet called DOG. I can then autofilter and use a pivot table on the DOG sheet.
I cant just use an autofilter, because the row below can and sometimes does appear elsewhere in the spreadsheet, but refers to something else. It is all because this is an imported text file.
I have attached the spreadsheet to show you what I mean.....

here is the code I have so far, modified from another thread I posted.

Sub dogs()
    Dim rd As Worksheet, dg As Worksheet, i As Long
    Dim fRng    As Range, Crit
     
     Application.ScreenUpdating = False
     
    Set rd = Sheets("Pets")
    Set dg = Sheets("Dog")
    
    i = rd.Cells(Rows.Count, 1).End(xlUp).Row
    Set fRng = rd.Range("A4:A" & i)
     
    Crit = "DOG "
        
    With fRng
        If Application.WorksheetFunction.CountIf(fRng, Crit & "*") > 0 Then
            .AutoFilter field:=1, Criteria1:=Crit & "*"
            
            .Offset(2, 1).EntireRow.Copy


' I then need to paste the row below to the "dog" sheet, one row
'after the other


                                   
            .AutoFilter
      
        End If
    End With
    
    Application.ScreenUpdating = True
    
End Sub

The attached is a very cut down version of my spreadsheet...
any help would be much appreciated.

Re: Delete Worksheets Based on Name

I meant delete.
thanks the above code works great.
Think I will use that [COLOR="Red"]CASE[/COLOR] method regularly in my code

Re: Find all Occurences Of Text in Column & Offset to Add Data

thanks mate.
That works perfectly
I guess it had something to do with not being a perfect match on the string.
cheers

Hi,

I am looking to close all the open sheets in an excel spreadsheet called findfees.xls, except for 2 sheets called master Data and scripts.

could someone help me with this code,
I always seem to be so close, and yet so far!

Sub closesheets()






Dim ws As Worksheet


data = "Master Data"
script = "script"


For Each ws In Worksheets
        If ws.Name <> data.Name Or script.Name Then
            ws.Delete
             
        End If
 Next ws
   
   
End Sub

Is it because I am not specifying the workbook property properly?

thanks

Re: Find all Occurences Of Text in Column & Offset to Add Data

Here is my sample file.
(there is meant to be 2 spaces between "FEE" and "£20.00".)
I am sure it has something to do with the fact I am copying the data from another advancefiltered sheet into this one.
I have typed the exact phrase into any of the cells in column A and run the sub and it works fine.
I have copied cell A10 into the code as well and run it - to no avail...
any ideas please?

Re: insert value in next column depending value in previous column

Quote from thomach

... since you were getting no changes, I also suspect that your "UNAUTH O/D FEE £20.00" does not exactly match the contents of the column A cells. Perhaps they have an extra space included. ()Bote: you specification of equal to requires an exact match.) You may want to use TRIM and or CLEAN as well as UCASE to remove leading/training extra characters. Also, you could compare the LEN before and after a SUBSTITUTE if you don't require a full cell content match.

Thanks for the prompt reply guys...
Still having problems with this. As Thomach has said it could be something to do with needing an exact phrase? Could you tell me more about the substitute or LEN command. I have downloaded your example spreadsheet and your code works, but on my data it doesnt. I have even copied in my data onto your sample spreadsheet and it doesnt work. Again, no error message recieved, and the code looks like its executing ok, but no changes... I have turned automatic calc on and off as well to no avail...
I cant attach my worksheet -probably because its my first post.
this is data that I have copied and pasted to a new sheet (fees) after advance filtering a master document - could that be a potential reason why its not working? If I type in the the exact phrase on Thomach attachment it works fine.... again any help her would be great
thanks

Hi ,
I have been unable to get this code quite right.

I want to be able to search all of column A for the string "UNAUTH O/D FEE £20.00" and when it is found, add £20 to column B, and 01/01/00 into column D of the same row.

Sub feedate()




    Set rd = Sheets("fees")
         
     z = 20
     x = 1 / 1 / 6
     
    For i = 1 To rd.Range("A65536").End(xlUp).Row
        If UCase(Cells(i, 1)) = "UNAUTH O/D FEE £20.00" Then
           Cells(i, 2).Value = z
           Cells(i, 3).Value = x
        End If
    Next i
End Sub

Think I am there, or there abouts, Im not getting any error codes or anything, in fact it runs but no changes occur after running....

Any ideas anyone would be much appreciated.

thanks