worksheet function – Excel VBA – Add LEFT Formula and Sort A to Z

I have a VBA code to re-arrange the column from Sheet 1 to Sheet 2 in a particular format

Now when VBA code is run it populates the data correctly in Sheet 2 in Columns A to E and Column G

I want to add a Formula in Column F where it should return certain data from Column B

Formula for Column F: “=LEFT(RIGHT(B2,10),9)

This formula should be executed until last blank row

Secondly, I would like to arrange this Sheet 2 alphabetically (a to z) based on column A

My VBA Code is:

Sub ModdedMap()

Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim HeadersOne As Range, HeadersTwo As Range
Dim hCell As Range

With ThisWorkbook
    Set Sh1 = .Sheets("Sheet1")
    Set Sh2 = .Sheets("Sheet2")
    Set Sh3 = .Sheets("Sheet3")
End With

Set HeadersOne = Sh3.Range("A1:A" & Sh3.Range("A" & Rows.Count).End(xlUp).Row)

Application.ScreenUpdating = False

For Each hCell In HeadersOne

    SCol = GetColMatched(Sh1, hCell.Value)
    TCol = GetColMatched(Sh2, hCell.Offset(0, 1).Value)
    LRow = GetLastRowMatched(Sh1, hCell.Value)
    
    For Iter = 2 To LRow
        Sh2.Cells(Iter, TCol).Value = Sh1.Cells(Iter, SCol).Value
    Next Iter
    
Next hCell

Application.ScreenUpdating = True
End Sub

Function GetLastRowMatched(Sh As Worksheet, Header As String) As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetLastRowMatched = Sh.Cells(Rows.Count, ColIndex).End(xlUp).Row
End Function

Function GetColMatched(Sh As Worksheet, Header As String) As Long
    ColIndex = Application.Match(Header, Sh.Rows(1), 0)
    GetColMatched = ColIndex
End Function

Appreciate any help

Cannot add events to Google Calendar using Windows Calendar

I have been using Windows calendar to manage my Google Calendar for the last 2 years. And it was working flawlessly. Now when I try to add events to my calendar thru Windows calendar I am getting following error message:

“something went wrong and we were unable to save your recent edits “

and event is NOT added to Google. I tried everything: removed/added back account, uninstalled and installed the mail app but no results. it is so frustrating.

Does anybody have similar issues.

I always keep my laptop updated with all the Windows updates.

python – Randomly selected integers from predetermined lists add up to specific input value

Hello im trying to write a Python code that selects integers from arrays up to a certain value.

I wrote this code however the code uses every list once (as is intended). But sometimes it is still below the input value and has no more options to choose from.

For example if i say WantedNumber is 15 and it picks 4 from listC and 5 from listA, it then has no more options for a correct answer in listB.

or sometimes it picks 1 from every list and then it has no more lists to choose from cause it used all of them already.

How can I make it select a random sequence of integers that adds up to WantedNumber

Note: ”similar questions” are always about completely random numbers adding up to input value. But I don’t want random numbers, I want randomly selected predetermined numbers from my lists.

import random

WantedNumber = input('please input your wanted number:')

listA = (1,2,3,4,5)
listB = (1,2,3)
listC = (1,2,4,7)

All = (listA,listB,listC)

x = 0

while x < int(WantedNumber):
    print(x) #this prints the new x value with the new Random2 added up
    
    Random1 = random.choice(All)
    Random2 = random.choice(Random1)

    print(Random1) #this prints which variable of the list it chose
    print(Random2) #this prints which integer from the array from Random1 it chose
    index = All.index(Random1)
    All.pop(index)

    Y = int(x) + int(Random2)
    
    if Y<=int(WantedNumber):
        x=Y
```

Socks5 Proxy Service Cheap Socks5


SOCKS Proxy List by Tisocks.net
If you Need Socks5 , Please visit service and add fund via PM , BTC WMZ . Thanks all!!
Add fund : https://tisocks.net/addfund
Check socks5 Online here : https://checksocks5.com
LIVE | 103.241.227.117:6667 | 4.001 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 5.56.61.183:30600 | 1.577 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 212.129.49.38:43771 | 0.972 | SOCKS5 | Tasmania | 7000 | Telstra Internet | Australia | Checked at https://tisocks.net
LIVE | 97.74.134.60:64622 | 2.092 | SOCKS5 | Unknow | Unknow | ip-97-74-6-64.ip.secureserver.net | United States | Checked at https://tisocks.net
LIVE | 182.87.183.8:45301 | 1.462 | SOCKS5 | Jiangxi | Unknow | China Telecom | China | Checked at https://tisocks.net
LIVE | 220.248.188.75:17211 | 1.182 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 72.210.252.134:46164 | 4.632 | SOCKS5 | Unknow | Unknow | Cox Communications | United States | Checked at https://tisocks.net
LIVE | 184.178.172.5:15303 | 4.424 | SOCKS5 | Unknow | Unknow | wsip-184-178-172-5.rn.hr.cox.net | United States | Checked at https://tisocks.net
LIVE | 174.76.35.29:36177 | 3.023 | SOCKS5 | Unknow | Unknow | Cox Communications | United States | Checked at https://tisocks.net
LIVE | 184.75.247.29:52117 | 6.035 | SOCKS5 | Unknow | Unknow | smtp.mattressoverstockusa.com | United States | Checked at https://tisocks.net
LIVE | 47.49.12.168:61554 | 2.411 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 139.162.76.176:19151 | 2.574 | SOCKS5 | Unknow | Unknow | Markus Koch | Unknow | Checked at https://tisocks.net
LIVE | 88.202.177.242:1090 | 0.218 | SOCKS5 | North Holland | 1012 | 58cab1f2.setaptr.net | Netherlands | Checked at https://tisocks.net
LIVE | 174.70.241.8:24398 | 3.16 | SOCKS5 | Unknow | Unknow | Cox Communications | United States | Checked at https://tisocks.net
LIVE | 98.162.25.29:31679 | 5.504 | SOCKS5 | Unknow | Unknow | Cox Communications | United States | Checked at https://tisocks.net
LIVE | 184.178.172.13:15311 | 5.494 | SOCKS5 | Unknow | Unknow | wsip-184-178-172-13.rn.hr.cox.net | United States | Checked at https://tisocks.net


tisocks
Reviewed by tisocks on
.
[Tisocks.net] – Socks5 Proxy Service Cheap Socks5
SOCKS Proxy List by Tisocks.net
If you Need Socks5 , Please visit service and add fund via PM , BTC WMZ . Thanks all!!
Add fund : https://tisocks.net/addfund
Check socks5 Online here : https://checksocks5.com
LIVE | 103.241.227.117:6667 | 4.001 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 5.56.61.183:30600 | 1.577 | SOCKS5 | Unknow | Unknow | | Unknow | Checked at https://tisocks.net
LIVE | 212.129.49.38:43771 | 0.972 | SOCKS5 | Tasmania | 7000 | Telstra

Rating: 5



.

Workarounds for quick add in Google Calendar?

I often get event information in Text format like this:

Fri 7:30 Live From the Spanish Ballroom: Gretchen Midgley
Fri 8pm Evelyn and her New Orleans Jazz Band - LIVE from NOLA
Sat 6:30 Movin' & Groovin'! Featuring Amy Johnson & Todd Yanacone
Sat 7pm The Salon: Quarantine Speakeasy (Oct 24th)
Sun noon Solo Jazz with Nils Andren!
Sun 2pm Performance and Fundraiser for Furloughed Knoxville Symphony Musicians

Entering this info into my Google Calendar by hand is onerous to say the least. There used to be a quick add but there is apparently a patent issue around this with Microsoft (?) so this was removed from Google Calendar and Gmail?

Workaround 1: Mac Calendar / Mac Mail (partial solution)

I have my Mac Calendar synced with Google Calendar. If I email myself this data there is partial detection of the event, and when I hover over the time I get an arrow I can click to add to my Mac Calendar, but it doesn’t get the title right (it’s) blank.

Any other ideas?

8 – How can I add “data-target” additional attribute in mega menu?

How can I add an additional attribute to menu item I am using mega menu?

On element inspect my current menu url is displaying as :

<a class="we-mega-menu-li activeNav" title=""href="https://drupal.stackexchange.com/clients/drupal/abc/fr/home#xyz" target="">
 something
</a>

I need to create it as :

<a class="we-mega-menu-li activeNav" title=""  data-target="xyz" href="https://drupal.stackexchange.com/clients/drupal/abc/fr/home#xyz" target="">
 something
</a>

I have used menu links attribute module but its not working with mega menu
any other suggestions by doing some custom code?

Add to Cart button displaying twice on Shop Page Woocommerce

Stack Exchange Network


Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

gui design – Is it a good idea to add new rows on top of existing ones?

The highlight is really helpful to attract the attention of the user, you should make it longer or change it into a fading effect, but the idea is to call the attention to the row for more time, maximizing the chances of noticing the change on the table.

If the design of the table is as the one on the fiddle, I find a problem, I’m assuming that A, B and C will be some kind of heading for the columns. There should also be a heading for the remove row column and it would be better if there is some change in colour for that one since it’s not data but an action, which means that is a different category of information/interaction.

Finally, about your question, adding the new row on top, or as the first one, makes sense, no just because of the position of the button, but because it’s going to be close to the headings and where the user attention is most probably going to be. Although, if the table may be long enough, or if the user is going to be checking data along the table, the adding control is going to be too far, so when still a good one, you may consider adding a new control, for instance a floating one, with it’s explanation, of course. You may also consider repeating the headers section which improves usability and gives you a place to have the plus button.

If you repeat headings, and then have more addition buttons, whenever the user clicks on it and adds a new row, it should be inserted right below the control, like in your actual example.

If sorting is important, then after an insertion happens, you should also provide input to the user and a button to press and resort the data.

How can I configure Google Calendar so that it doesn’t automatically add calendar events from emails that were classified as spam?

How can I configure Google Calendar so that it doesn’t automatically add calendar events from emails (Gmail) that were classified as spam, while still automatically adding calendar events from non-spam emails?

Hibernate – how to add table not business related (Spring Boot)

I wonder what is the best practice in my case:
I have table A and I want to add table B that is not business related to table A, so data in table B should be used only to sort data from table A.

So in that case there is a wrong approach to add foreign key in table A to table B.

I see 2 options:

  1. Table B has foreign key to table A but in that case entity A, after fetching data from table A will load also data from table B? I’d like to get data from table B only on demand and I don’t want to have data from table B when I need only data from table A
  2. I have foreign key in table B that points to table A and nothing in entity A so I have to manually get data from table B for some specific row in table A.
@Table(name="TableA")
public class A{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)

@OneToOne(cascade = {CascadeType.MERGE, CascadeType.PERSIST})
@JoinColumn(name="tablea_id", unique="true)
private ObjectA objA;

}

What’s the best solution in my case? I want to get data from table B only in some rare cases…