Posts by gazzauk

I can't get my head around this on a Friday afternoon, spent 2 hours using combination of IF AND VLOOKUP but can't get it to work, I need to know the followng
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="colspan: 7"]if Bob is in list 2 and has the skill in column B, return date of skill here[/TD]

[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 64"]LIST1[/TD]
[TD="width: 93"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]LIST2[/TD]
[TD="width: 64"] [/TD]
[TD="width: 75"] [/TD]

[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 64"]Employee[/TD]
[TD="width: 93"]Skill[/TD]
[TD="width: 64"]Date[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]Employee[/TD]
[TD="width: 64"]Skill[/TD]
[TD="width: 75"]Date[/TD]

Bob

Ice Cream

[TD="colspan: 7"] [/TD]

Sam

Potato

[TD="align: right"]01/01/2018[/TD]

Sam

Potato

Bob

Ice Cream

[TD="align: right"]02/01/2018[/TD]

Geoff

Cabage

Geoff

Cabage

[TD="align: right"]03/01/2018[/TD]

[/TABLE]

Re: vba to write batch file

Quote from Grimes0332;765916

I would think the 'correct' answer, for other occasions where you don't have an opportunity to offset the range you're exporting, is as I posted. It didn't work only because your looping logic was wrong.

(Honestly, this is not a case of sour grapes - the original PRINT statement offers all the flexibility to do what you want so why not use it?)

Hi,

I am using your looping method with the -1 from S O

Gary

Re: vba to write batch file

Quote from rory;765912

Why are you using Cell.Offset(1, 0)?

Took that out now, got messy trying different fixes

Re: vba to write batch file

Quote from S O;765911

I think this is your cuplrit:

Code

For Each Cell In Range("T1:T" & Cells(Rows.Count, "T").End(xlUp).Row)
Print #FileNumber, Cell.Offset(1, 0)
Next Cell
Close #FileNumber

You're looping each cell within the data of column T, but you're printing the offset of that cell - so when you get to the last cell you're just print a blacnk cell (because the offset of the last cell with data will be blank)

Try using this instead:

Code

For Each Cell In Range("T1:T" & Cells(Rows.Count, "T").End(xlUp).Row -1) '// Stop looping one cell before the end instead.
Print #FileNumber, Cell.Offset(1, 0)
Next Cell
Close #FileNumber

Display More

Thankyou the -1 solved it

Re: vba to write batch file

Quote from Grimes0332;765907

For infomation. 'PRINT' appends a Carriage Return to each line printed which is why you get that.

Slight change. Note the implicit vbCRLF's and the terminating ';' which suppresses the auto line feeds.

Code

Sub x()




   Const FileName = "C:\test.bat"
   Dim FileNumber       As Integer
   Dim retVal           As Variant




   FileNumber = FreeFile
   Open FileName For Output As #FileNumber




   ' Changing cmd prompt directory, to users choice.
   Print #FileNumber, "@echo off"
   Print #FileNumber, "C:";








   For Each cell In Range("T1:T" & Cells(Rows.Count, "T").End(xlUp).Row)
      Print #FileNumber, vbCrLf & cell.Text;
   Next




   Close #FileNumber
   
End Sub

Display More

Thanks, tried it but still got the same issue on last line.

Gary

Re: vba to write batch file

Quote from S O;765903

Why would a blank line cause a problem in a batch file?

Not disputing it, just wondering if the problem is actually being caused by something else?

Hi, I am using it to copy files from location A to B, with the blank it created a line copy c:\test\ c:\test1 which copies everything.

Hi,

I have created some VBA to write a batch file, however it's writing a blank line at the bottom, which is causing issues, any ideas on how to correct this?

' Set FileName for bat file




Const FileName = "C:\test.bat"
Dim FileNumber As Integer
Dim retVal As Variant




FileNumber = FreeFile
Open FileName For Output As #FileNumber




' Changing cmd prompt directory, to users choice.




Print #FileNumber, "@echo off"
Print #FileNumber, "C:"




For Each Cell In Range("T1:T" & Cells(Rows.Count, "T").End(xlUp).Row)
Print #FileNumber, Cell.Offset(1, 0)
Next Cell
Close #FileNumber

Re: Prevent list box from automatically resizing

forum.ozgrid.com/index.php?attachment/65424/

after you generate a list click on design mode, each time you do it gets smaller and smaller.

Re: Prevent list box from automatically resizing

[ATTACH=CONFIG]65423[/ATTACH]

You can see the list is being created in the list box

Re: HELP! List box keeps imploding

Hi,

tried that still resizes

Gary

I Have a list box which worked fine, however when I move it to a computer with a different resolution, the list box implodes on itself, shrinking the list till you can't read it, it also resizes the list box to match the width of the entries, I have tried everything but cannot lock it down,

Any suggestions?

Gary

Re: vba to prevent data duplication

Quote from cytop;746945

Probably easier to use CountIF()

Code

If WorksheetFunction.CountIf(Range("A1:A9999"), "Test") > 0

(Typed freehand - replace "Test" with your variable)

Did something along these lines originally, but the blanks rows were causing an issue.

Hi,

Im trying to write a piece of code to prevent duplicate data entry, but my head has hit a brick wall, and ideas?

Thanks

'validate text box      


        Value_Enter = INDEX.Value
       With Worksheets("Data").Range("A1:A9999")
        Set C = .Find(Value_Enter, LookIn:=xlValues)
        If C > 0 Then MsgBox ("Data already entered, please use the update form to amend!") 




       End With




End Sub

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Thanks once again!, you're a star

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Hi,

Just one quick Q.. is there any way of sorting list is Alphabetical order?

Gary

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Thankyou very much!!!

works like a dream

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Quote from KjBox;746260

OK thanks. It is 11PM for me now, I will get onto this first thing in the morning.

Thankyou, there's no rush.

Gary

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Quote from KjBox;746252

I assume you want 2 list boxes on sheet 5, the first to load with all possible products from column G of Data Sheet and the second to load with the Operating Systems from column H of Data.

Yes please

Quote

Do you want the list boxes to load when the button is clicked, or to be loaded all the time and be updated when Data sheet changes?

When button is clicked from Control tab

Quote

Do you want list box 2 to show every possible operating system or just to load with the relevant Operating Systems for whatever Product is selected in List Box 1?

does not matter, would be nice if it did, but not a show stopper

Quote

You say that when both list boxes have an item selected the returned result should be the row(s) where the product and operating system match those selected, where do you want that result displayed?

New Workbook

Many Thanks

Gary

Re: $50 - Take value from 2 multi listboxes and search row for criteria

Quote from KjBox;746239

I can look at this for you.

Please attach a sample workbook rather that an image

Hi,

I have attached a sample.

Controls are in first tab.

Thanks

Gary

10% Paid

Hi,

I have a form with 2 multi listboxes (listbox1 and listbox2).

I would like to take the values of these listboxes and search for (contains within) the output of listbox1 in column G and search for (contains within) the output of listbox2 in column H.

Once a match has been found I would like to copy the entire row(s) to a new sheet

Example

[ATTACH=CONFIG]65254[/ATTACH]

Thanks

Gary