0

I am looking to make a macro to find if a patient has certain complications .

For example list of conditions patient A has: "Urinary calculus, unspecified; Unspecified joint derangement; Fibrosis and cirrhosis of liver; Liver cell carcinoma", and I want to find if patient A has "Fibrosis and cirrhosis of liver;", and if yes, I want to print it into another cell.

The following is what I have done so far:

'column C (all complications)
Dim complications_allCol As String
complications_allCol = "C"
complications_allColN = Range(complications_allCol & 1).Column
SumSheet1.Range(complications_allCol & "1").Value = "Complications (All)"
SumSheet1.Range("C1").ColumnWidth = 62
SumSheet1.Range("C1").WrapText = True
PDD.Range("BM" & copystartrow & ":BM" & PDDlastrow).Copy
SumSheet1.Range("C" & pastestartrow).PasteSpecial Paste:=xlPasteValues

'column D (existing complications)
Dim complications_originalCol As String
complications_originalCol = "D"
complications_originalColN = Range(complications_originalCol & 1).Column
SumSheet1.Range(complications_originalCol & "1").Value = "Complications (Original)"
SumSheet1.Range("D1").ColumnWidth = 62
SumSheet1.Range("D1").WrapText = True

Dim mainstring As String
Dim substring As String
mainstring = SumSheet1.Cells(pasterownum, "complications_allCol").Value
substring = "Unspecified disorder of synovium and tendon, site unspecified"
If InStr(mainstring, substring) <> 0 Then
    pastesht.Cells(pasterownum, "D").Value = "Unspecified disorder of synovium and tendon, site unspecified"
End If

But for some reason I am getting a type mismatch error at

mainstring = SumSheet1.Cells(pasterownum, "complications_allCol").Value

Could anyone have a look through what I have done and let me know if there's anything wrong/any improvements I can make for this idea?

Shawn Alcantara
  • 31
  • 2
  • 1
    You enclosed quotes around `complications_allCol` which makes it a literal string instead of referring to the variable itself. Try `SumSheet1.Cells(pasterownum, complications_allCol).Value` – Raymond Wu Jul 13 '22 at 01:21
  • @RaymondWu tried it but it gave 'application-defined or object-defined error' – Shawn Alcantara Jul 13 '22 at 02:11
  • What is the value of `pasterownum`? You didn't declare nor assign a value to `pasterownum` in the above code so if that is all your code then `pasterownum` is `0`. This would mean you are referring to cell `C0` which doesn't exist. Please declare all your variables (insert `Option Explicit` at the top of the module to help you enforce it) – Raymond Wu Jul 13 '22 at 02:19
  • hmm i see is there any other way for me to do a search at a row level? for example to search if row 1 column C contains "abcde" then paste "abcde" in row 1 column D, then move on and search row 2 column C for "abcde" and paste it into row 2 column D and so on? – Shawn Alcantara Jul 13 '22 at 02:29
  • This seems like something you can do using excel formula so no need VBA, something like `=IF(NOT(ISERROR(FIND($B$1,A2))), $B$1,"")` where `B1` is the find text and `A2` is the list of conditions. If you insist to use VBA then look at [Range.Find](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) method @ShawnAlcantara – Raymond Wu Jul 13 '22 at 02:41

0 Answers0