Extracting the column index from table name
I have a Table(myTable) With which I am working in VBA and want to extract the column index of a particular column(col_1) so that I can use it in Cells(row,col) for further calculation.
Is it possible? how is it done ?
See also questions close to this topic
AppleScript for Extracting Email MS Outlook 2016 and saving it to Excel
Need help in figuring out how to extract content from an email on MS Outlook, and saving it on MS Excel. I tried below but I'm getting stuck with figuring out how to select the specific email folder of an inbox I want to extract from.
Hope someone could help.
tell application "Microsoft Excel" set LinkRemoval to make new workbook set theSheet to active sheet of LinkRemoval set formula of range "D1" of theSheet to "Message" set formula of range "C1" of theSheet to "Subject" set formula of range "B1" of theSheet to "From" set formula of range "A1" of theSheet to "Date" end tell tell application "Microsoft Outlook" activate set myAccount to exchange account 2 set theMessages to messages of inbox set theRow to 2 repeat with aMessage in theMessages my SetDate(date received of aMessage, theRow, theSheet) my SetFrom(sender of aMessage, theRow, theSheet) my SetSubject(subject of aMessage, theRow, theSheet) my SetMessage(content of aMessage, theRow, theSheet) set theRow to theRow + 1 end repeat end tell on SetDate(theDate, theRow, theSheet) tell application "Microsoft Excel" set theRange to "A" & theRow set formula of rangetheRange of theSheet to theDate end tell end SetDate on SetFrom(theSender, theRow, theSheet) tell application "Microsoft Excel" set theRange to "B" & theRow set formula of rangetheRange of theSheet to theSender end tell end SetFrom on SetSubject(theSubject, theRow, theSheet) tell application "Microsoft Excel" set theRange to "C" & theRow set formula of rangetheRange of theSheet to theSubject end tell end SetSubject on SetMessage(theMessage, theRow, theSheet) tell application "Microsoft Excel" set theRange to "D" & theRow set formula of rangetheRange of theSheet to theMessage end tell end SetMessage
Range fails if sheet is not selected
I have this code which should work fine
Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Auswertung") ... ws.Range(Cells(2, 2), Cells(lastRow, lastCol)).NumberFormat = "0"
It however fails if the sheet is not selected. Error 1004, Methode Range fails for the object.
If I change it to
ws.Select ws.Range(Cells(2, 2), Cells(lastRow, lastCol)).NumberFormat = "0"
why? In VBA I want to avoid
How to clean up my messy Super Smash Bros Excel data using Python, pandas, etc. so I can load it via ETL into SQL? Pictures attached
I'm working on a personal project of mine regarding Super Smash Bros to improve my SQL, ETL, Python/pandas, and eventually data science skillset (once I load all the data in). The first picture below is what part of my excel data sheet looks like when I read it into pandas. It is essentially 12 months of fake Smash Bros fights, displayed horizontally. However, I need to iterate through rows to so I can alter the sheet and make it into a more digestible format for an ETL tool to handle.
The first step I believe is to stack every month's data on top of one another vertically so iteration is far easier. I believe it would look something like this picture below.
And finally this is eventually how I want the data to look so I can load it directly into SQL. Using the very first "fight" as an example, you can see Richter Belmont faced Dr. Mario and won 2-0 in Month 1, Week 1.
- Fight_ID is a an ID to be able to connect two fighters who faced one another.
- Result_ID is a unique identifier for each fighter in a fight
- Decision is a W/L for Win/Loss
- Match_Result is lives (stocks) remaining
- Fight_Type is usually 3 stock unless specified in the columns left of the specific fight. For example, there is nothin to the left of the 1 in the Richter Belmont/Dr. Mario row therefore it is a 3 stock match.
- Location needs to somehow be grabbed through each iteration and assigned to every fight/result in that same month. Same thing needs to be down with Brand and Month because they originally only appear one time in the Excel sheet. Week will also change each time the Location changes.
- Some of the columns can remain NULL as the information doesn't need to be filled in these cases.
I guess my question is how to get from point A to point B (and if you have suggestions to Point C as well!). I don't even need specific code how to get to Point C, just a starting point or some suggestion concepts on how to do it!
See available methods of ActiveX object
I can create
ActiveXobject using interface of custom app:
Dim CustomObject As Object Set CustomObject = CreateObject("CustomApp.CustomOleInterface")
But I don't know API of this interface. Can I see available methods for
CustomObject? Or can I dump object to file for viewing it in HEX-redactor?
How to remove dynamic digits of prefix
I want to change the format of the values in the Array(OutArray) mentioned in the below code. The Array will return as six digit numbers, with preceding 0's and with signs(+ or -) to make up the numbers. If the actual values are 5678, -5000, 8, the Array will return as +005678, -005000, +000008. I couldn't change this datatype format since its the only way the instrument will return data.
I'm trying to remove the Prefix 0 and also the '+' that comes along with each value. But the class is that, I don't know how many 0's will be there as Prefix since the values returned are dynamic. I'm struck in this line of code,
InArray = MID(Step, "", "")
Where I actually wanted to identify the first non-zero value. Is there any way to do this?
Public Function copy_XY() Dim idn As String idn = instrument.ReadString() ' Reading a value from instrument (Dynamic) Dim OutArray() As String OutArray = Split(idn) Dim Step As Variant Dim InArray As String For Each Step in OutArray InArray = MID(Step, "", "") Next Step End function
When I use CInt as below I'm getting overflow error.
Dim Val() As Int Val(0) = CInt(OutArray(0))