# Posts by black31cat

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.

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"]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/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.

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"]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[tr]

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

[/tr]

[/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:

Code
``````If cbxbl.Value Then
i = 29``````

make it like this:

Code
``````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):

Code
``str = StrConv(Target.Value, vbProperCase)``

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

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

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

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

Code
``````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.

Code
``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:

Code
``Dim orgVal As Integer``

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

Code
``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: