You have a red exclamaition mark on your project. Eclipse is likly to not build your project if build path problems (e.g.) aren’t resolved. So go to the Problems tab and try to resolve your errors there. If that is finished Eclipse will build your project and gives more helpful errors Edit and that is … Read more
In VBA, there is the AscW function that returns the Unicode code point for the first letter of a string as a decimal number. Use the following code snippet as an example: Sub mycode() MsgBox “female symbol code = ” & CStr(AscW(Cells(1, 1).Text)) & “, male symbol code = ” & CStr(AscW(Cells(2, 1).Text)) End Sub … Read more
If you want the name in column E and the worked hours in column F then set E1 to =IF(B1>0,A1,””) and set F1 to =IF(B1>0,B1,””) If you want the name and worked hours both in column E then set E1 to =IF(B1>0,CONCATENATE(A1,” “,B1),””) and copy down the column. IF(condition, expression if true, expression if false) … Read more
Count of Shift Combinations within a List of 100 continuous Shifts This answer is a solution to the revised requirements of the asker. I’m posting this answer as the asker changed the original requirements, and therefore requiring a different solution than the first posted. I decide to leave both solutions for the benefit of other … Read more
First, This will not paste any headers. Since they are all the same, just move the headers to the excel that will store the consolidation. (No need for a macro to do this since it only needs to happen once). Second, in the code you need to change “SHEETNAME?” to the name of the sheet … Read more
If I am seeing what you are trying to do, it shouldn’t be too hard. You could hardcode a for loop to 800. for i = 2 to 800 Range(“A”&i).Copy Destination:=Sheets(i).Range(“A” & i) next This is similar albeit a bit more involved solved In main sheet there is 800 names(A1:A800). Each cell should go to … Read more
It seems that in the documentation of xlwings, it is necessary to have an empty row and column at the bottom and to the right. if not it will overwrite it http://docs.xlwings.org/en/stable/api.html#xlwings.xlwings.ret solved xlwings udf function erase next cell on excel
Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed. Sub TransposeData() Const FirstDataRow As Long = 2 ‘ presuming row 1 has headers Const YearColumn As String = “A” ‘ … Read more
Please never use reserved words like list, type, id… as variables because masking built-in functions. If later in code use list e.g. list = data[‘FirstNames’].tolist() #another solution for converting to list list1 = list(data[‘SecondNames’]) get very weird errors and debug is very complicated. So need: L = data[‘FirstNames’].tolist() Or: L = list(data[‘FirstNames’]) Also can check … Read more
Use the next function, please: Function newID(sh As Worksheet) As String Dim lastR As Long, strID As String, arr As Variant, i As Long lastR = sh.Range(“A” & Rows.count).End(xlUp).Row arr = sh.Range(“A2:A” & lastR).Value strID = left(sh.Range(“A2”).Value, 4) For i = 1 To UBound(arr) arr(i, 1) = CLng(Right(arr(i, 1), 3)) Next i newID = strID … Read more
Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function¹ and the updated CONCAT function² have difficulty with conditional concatenation beyond TEXTJOIN’s blank/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng As … Read more
So is it a text file or excel file you are importing? Without more details, file structure, or a reproducible example, it will be hard to help. You can try: # Get the correct package install.packages(‘readxl’) df1 <- readxl::read_excel(‘[directory name][file name].xlsx’) df2 <- readxl::read_excel(‘[directory name][file name].xlsx’) # Creates a new variable flag if miRNA from … Read more
The comments actually helped me get the answer by pointing me to openpyxl. I’m posting it here if anyone else had it. import openpyxl wb = openpyxl.load_workbook(‘Roster Report.xlsx’) header_text = str(wb.active.HeaderFooter) wb.close() I didn’t see a way in xlrd to read the header, only to write it. 2 solved Read Excel Text Header Using Python
Something like this might work in the ThisWorkbook module’s, Workbook_Open event. Private Sub Workbook_Open() Dim ws As Worksheet Dim mnth As String, dte As String, mday As String mday = Now() – Weekday(Now(), 3) mnth = Month(mday) dte = Day(mday) tabstr = mnth & “-” & dte For Each ws In Worksheets If ws.Name = … Read more
Collect the data into a variant array Process the collation within the array backwards Dump the data back to the worksheet Remove duplicates based on the first column. Code: Option Explicit Sub Macro1() Dim i As Long, j As Long, arr As Variant With Worksheets(“sheet10”) ‘Collect the data into a variant array arr = .Range(.Cells(2, … Read more