VBA - Creating a macro for reducing text in cell

Guys,

I have been pointed here from another thread as am trying to create a VBA macro that when I press a button the following occurs....


Column B (the date and time) reduced from "20190520 07:38:31.332442 +0000" to just "20190520" which just shows the date.

and

Column U reduced from "20190520 07:38:31.332442 +0000" to just "07:38:31"

and

IF Cell A2 is populated then Cell N2 will be populated with "N" (unless i overwrite)

Of course I will have multiple lines on the spreadsheet so we assume the macro will perform this across all lines.

thanks in advance

Possibly...

Please Login or Register  to view this content.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by Marc L

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks Marc, didn't scroll down to see "Manage Attachments". Thanks for your efforts.

Originally Posted by moreno5

Column B (the date and time) reduced from "20190520 07:38:31.332442 +0000" to just "20190520" which just shows the date.

Column U reduced from "20190520 07:38:31.332442 +0000" to just "07:38:31"

try

Please Login or Register  to view this content.

Originally Posted by moreno5

IF Cell A2 is populated then Cell N2 will be populated with "N" (unless i overwrite)
Of course I will have multiple lines on the spreadsheet so we assume the macro will perform this across all lines.

Don't understand above.

Originally Posted by jindon

try

Please Login or Register  to view this content.

Don't understand above.

Essentially if a cell is populated in column A...for example A3 has an ID then N3 should be populated with an "N". The same would go for if an ID was now in cell A4 then N4 should be populated with an "N".

Not really sure for the second part.
This will change to "N", if Col.A<>"" and Col.N = "", and no change if col.N has some value already.

Please Login or Register  to view this content.

Originally Posted by moreno5

Thanks Marc, didn't scroll down to see "Manage Attachments". Thanks for your efforts.

A starter to paste to the Options booking worksheet module :

PHP Code: 

Sub Demo1()
    With [A1].CurrentRegion.Rows("2:" & [A1].CurrentRegion.Rows.Count).Columns
        .Item(2).Value = Evaluate("IF({1},LEFT(" & .Item(2).Address & ",8))")
        .Item(14).Value = Evaluate("IF(" & .Item(1).Address & ">"""",""N"","""")")
        .Item(21).Value = Evaluate("IF({1},MID(" & .Item(21).Address & ",10,8))")
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Originally Posted by jindon

Not really sure for the second part.
This will change to "N", if Col.A<>"" and Col.N = "", and no change if col.N has some value already.

Please Login or Register  to view this content.

Perfect! That worked superbly. Thank you very much for your help.

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVE

Actually, sorry to be cheeky....just one last thing as it has worked so nicely.

Any chance we can have it so that again like with the first one where if cell A is populated with anything then cell N in the same line is populated with an "N"....can we also have the following populated into cell V on the same line "ICE.IFLO"

thanks

Try change to

Please Login or Register  to view this content.

Superb and thank you.

Hi Jin,

I have just found that I will need need to divide all the prices in column H by 100 with the macro also.

So again, is it possible to have that if cell A on the same line is populated, the figure populated in Cell H of the same line is divided by 100 (So A3 is populated then divide H3 by 100).

thanks,

Try change to

Please Login or Register  to view this content.

Thanks again!