Posts by black31cat

Help please. Thank you.

Hello. Good day! I have an inquiry with regards to copying multiple notepad data into the workbook I am using.

Current situation is code is running but it enters the data always into Cell A1 of the active sheet.

I need to modify the script to enter the data into different sheet according to the value found in DataArray(1,1). Sheets are named after the value found in that array. Thank you.

Sub DelimitedTextFileToArray()


Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim ArrTmp() As String
Dim TempArray() As String
Dim FileName As String
Dim Path As String
Dim rw As Long, col As Long
Dim FileCounter As Integer


Delimiter = ","
Path = InputBox("Enter path of text files: ")


For FileCounter = 1 To 8
    Select Case FileCounter
        Case 1
            FileName = "\FileOne.txt"
        Case 2
            FileName = "\FileTwo.txt"
        Case 3
            FileName = "\FileThree.txt"
        Case 4
            FileName = "\FileFour.txt"
        Case 5
            FileName = "\FileFive.txt"
        Case 6
            FileName = "\FileSix.txt"
        Case 7
            FileName = "\FileSeven.txt"
        Case 8
            FileName = "\FileEight.txt"
    End Select
          
FilePath = Path & FileName
rw = 0


TextFile = FreeFile
Open FilePath For Input As TextFile


FileContent = Input(LOF(TextFile), TextFile)


Close TextFile


LineArray() = Split(FileContent, vbCrLf)


For x = LBound(LineArray) To UBound(LineArray)
If Len(Trim(LineArray(x))) <> 0 Then
    TempArray = Split(LineArray(x), Delimiter)
    
    col = UBound(TempArray)
    
    ReDim Preserve DataArray(col, rw)
    
    For y = LBound(TempArray) To UBound(TempArray)
        DataArray(y, rw) = TempArray(y)
        Cells(rw + 1, y + 1).Value = DataArray(y, rw)
    Next y


End If


rw = rw + 1


Next x


Next FileCounter


End Sub

Re: count if A or B or C

I'm having trouble picturing what your worksheet looks like and what outcome you like. Maybe you can upload a sample worksheet for me to further understand what you mean? Please do not include confidential information if you decide to upload a worksheet.

Re: count if A or B or C

Experts, correct me if I'm wrong but I don't think COUNTIFS can be used this way. I use multiple COUNTIF for scenarios like this.

=COUNTIF(REPAIR!D8:D1007,"1 Minor")+COUNTIF(REPAIR!D8:D1007,"2 Re-Plate")+COUNTIF(REPAIR!D8:D1007,"4 Plate/Seam")

Re: Average Time

Thanks for this explanation! I got a clearer knowledge of array formula with this.

Re: Average Time

Hi XOR LX, that's superb! Thanks for the help! Just the formula I needed!

Can you explain a little bit about the formula? How does this array formula works? When to use it? Just a summary to give me a gist of it. Thanks!

Hi, how would I get the average time of a range?

My data looks like this:
[TABLE="width: 64"]

[TD="class: xl65, width: 64"]23:23:22[/TD]

[TD="class: xl65, width: 64"]23:45:34[/TD]

[TD="class: xl65, width: 64"]0:05:35[/TD]

[TD="class: xl65, width: 64"]23:16:35[/TD]

[TD="class: xl65, width: 64"]22:51:56[/TD]

[TD="class: xl65, width: 64"]4:00:28
[/TD]

[/TABLE]

If I use the Average formula (=Average(A1:A6)), I get the the answer 16:13:55 which is incorrect. The correct average would be 0:13:5.

Thanks in advance!

I'm just wondering if it is posible to execute commands in cmd and the output will be saved in the sheet?

For example:
Command = NET USER TEST /DOMAIN | FIND /I "Password Last Set"
Output = Password Last Set 10/23/2014

In this example, the output will be saved in Sheet1 Cell A1.

Thanks for the help.

Re: Duplicates Based on 2 Columns

Hi SM,
That's what I proposed. But the user doesn't want any extra columns. Even hidden columns are not allowed. That's why I am thinking of a way to do it via VBA.

Hi guys,

Here is the problem. I want to find duplicates based on 2 columns then color the entire row with red.

Example:
[TABLE="width: 96"]

[TD="width: 64, bgcolor: transparent"]Green[/TD]
[TD="width: 64, bgcolor: transparent"]Red[/TD]

[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]Green[/TD]

[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Green[/TD]

[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent"]Red[/TD]

[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Red[/TD]

[/TABLE]

In this example, the first AND fourth row will be highlighted with red (EntireRow.Interior.ColorIndex = 3).

Re: Concatenate with custom text format

Try this if this is what you like.

=CONCATENATE(A1,"|",B1,"|",C1,"|",TEXT(D1,"00000000000000.000000"),"|",E1)

Re: loop problem

upon looking at the codes, danarida is correct. you have problem with your if statement. your code is:

If cbxbl.Value Then
            i = 29

make it like this:

If cbxbl.Value = True Then
            i = 29

try this changes.

Re: loop problem

hello dragavs. since you already posted screenshots, can you also attach the file? this way, it will be easier to for us to test.

Re: Capitalize first letter of each word then replace space with underscore

Hi Smallman and Jindon,

Thanks for the help. Works perfectly!

If you may, can you provide a little bit of explanation for this solution? I want to further understand so next time I will remember it. Thanks alot!

Re: Capitalize first letter of each word then replace space with underscore

Yes, I tried doing that. And it creates an infinite loop as mentioned.

Hi,

I need vba to change the target value to capitalize all of the first letters then change the space with underscore.

I already got this code for the first part (capitalize all first letters):

str = StrConv(Target.Value, vbProperCase)

and this code for the second part (replace space with underscore):

Target.Value = Replace(Target.Value, " ", "_")

But when I try to combine them, it creates an eternal loop in the second part.

Pleade give some guidance. Thanks!

Re: Search a colum for multiple text strings and delete matches

Post a sample workbook to make this easier.

Re: Insert Rows Based on Cell Value

Sub drekole()
Dim cnt As Integer
For i = 1 To Range("B1048576").End(xlUp).Row
    cnt = Range("B" & i).Offset(0, 1).Value + 1
    For x = 1 To cnt
        Range("A1048576").End(xlUp).Offset(1, 0).Value = Range("B" & i).Value
    Next x
Next i
End Sub

Re: Changing of Font Colour using if else statement

Hi, this code is not like the one that you commented.

Set OriRange_1 = Range("B47") 'get the original number already outputted by previous coding run

You are only setting the range of OriRange_1 and not the value of it.

My suggestion is you declare a new Dim:

Dim orgVal As Integer

After this, assign the value of the cell to it:

orgVal = Range("B47").Value

Use this to compare with the cell_1.Value in your if statement.

Re: Code to Copy 2 Col to new sheet and delete rows without values (Repost)

You can try this one:

Sub falconbite()
Dim ws As Worksheet
Dim wn As String
For i = 1 To 3
    Sheets("Sheet1").Activate
    If i = 1 Then
        wn = Range("C1").Value
        Range("A:A, C:C").Copy
    ElseIf i = 2 Then
        wn = Range("D1").Value
        Range("A:A, D:D").Copy
    ElseIf i = 3 Then
        wn = Range("E1").Value
        Range("A:A, E:E").Copy
    End If
     
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = wn
     
    Range("A1").PasteSpecial
     
    Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    For ctr = 1 To Range("A1048576").End(xlUp).Row
        If Not IsNumeric(Range("B" & ctr).Value) Then
            Range("B" & ctr).EntireRow.Delete
            ctr = ctr - 1
        ElseIf Range("B" & ctr).Value = "0" Then
            Range("B" & ctr).EntireRow.Delete
            ctr = ctr - 1
        End If
    Next ctr
Next i
End Sub